How to insert special characters:& and ' (Multiple solutions) in Oracle

Oracle took the URL's parameter connector & as a custom variable, so I was asked to assign a value to the variable amp. What do we do? There are three methods:
·method One: Add set define off before the SQL statement to be inserted, and batch execution with the original SQL statement
When we execute the sql> Show all command under Sql*plus, we can find a parameter: Define "&" (Hex 26), as shown in
......
Concat "." (Hex 2e)
Copycommit 0
Copytypecheck on
define "&" (Hex 26)
Describe DEPTH 1 linenum off INDENT off
Echo OFF
......
This is the setting used in Oracle to identify the custom variables, and now we turn them off under Sql*plus:
sql> Set define OFF;
Then execute the import script again, ok! Problem is done.
Note: If you are executing in toad, it is recommended that the first line of the script to be imported, plus the previous one, be closed define, otherwise you will get an error when you import a second script that contains special characters.
If you are executing in sql*plus, you only need to set the define OFF once, and you can import it continuously later. Until you reset define on.
• Method Two: Replace ' & ' with Chr (38) in the SQL statement, because CHR (38) is the ASCII code of ' & '
sql> Select ' Tom ' | | Chr (38) | | ' Jerry ' from dual;
·method Three: Split the original string
sql> Select ' Tom ' | | ' & ' | | ' Jerry ' from dual;
As we can see, the method is the simplest and most efficient. Method Two because there is a procedure to call the function, so the performance is slightly worse. Method three needs two times the connection string, the efficiency is the worst!
So what if the contents of the field contain single quotes? For example: It ' s fine. Method also has three
• Method One: Use escape characters
SQL > Select ' Test ' | | "From dual;
Note: What does the "four single quotes" mean? First and last are the string connectors in Oracle, this is no objection. So what does the second ' and third ' mean? The second ' is an escape character
The third ' is our real content.
·method Two: The same is the use of escape characters, but the way is different
SQL > Select ' Test ' from dual;
Note: Here's the second, third ' is the escape character and the real content that we mentioned in method one above
• Method Three: replace ' in SQL ' with Chr (39) because Chr (39) is ' the ASCII code
SQL > Select ' It ' | | Chr (39) | | ' Fine ' from dual;

Reprint Please specify source: http://www.blogjava.net/pengpenglin/archive/2008/06/16/208436.html

How to insert special characters:& and ' (Multiple solutions) in Oracle