Resolution rollup for inserting special characters:& and ' in Oracle _oracle

Today, when you import a batch of data to Oracle, you have this problem: Toad prompted to assign to a custom variable amp, at first I was puzzled that the data is a series of INSERT statements, how can there be a custom variable? Then I searched the keyword amp and found that it was because there is a field in the insert data that reads as follows:

http://xxx.com/3DX?uid=0676&sid=rt_060908

Oracle put the parameter Connector for the URL here & as a custom variable, so I was asked to assign a value to the variable amp. After testing, the following three methods are summarized:

Method One: Precede the SQL statement to be inserted with the set define off; bulk execution with the original SQL statement

When we execute the sql> Show all command under Sql*plus, we can find one parameter: Define "&" (Hex 26), the code looks like this

......
Concat "." (Hex 2e)
Copycommit 0
Copytypecheck on
define ' & ' (hex)
describe DEPTH 1 linenum off INDENT off
Echo OFF
   ......

This is the setting that Oracle uses to identify custom variables, and now we turn it off under Sql*plus:

sql> Set define off;

Then execute the import script again, ok! The problem is fixed.

Note: If you are executing in toad, it is recommended that you turn off define in the first line of each script you want to import, or else you will get an error when you import the second script that contains special characters.
If you are executing in sql*plus, you only need to set the define off once, and then you can import it continuously. Until you reset define on.

Insert a statement:

sql> INSERT INTO AA (o,resvalue) VALUES (' AA ', ' S ' | | Chr (38) | | P ');

Method Two: Replace ' & ' in SQL statement with Chr (38), because Chr (38) is an ASCII code of ' & '

sql> Select ' Tom ' | | Chr (38) | | ' Jerry ' from dual;

Method Three: Split the original string

sql> Select ' Tom ' | | ' & ' | | ' Jerry ' from dual;

We can see that the method is the easiest and most efficient. Method Two because there is a process of calling a function, so the performance is slightly worse. Method three two times to connect string, the least efficient!

So what if the field contains single quotes in its contents? For example: It ' s fine. In this case there are also three ways:

Method One: Use escape characters

SQL > Select ' Test ' | | ' from dual;

Note: What do the "four" single quotes here mean? First and last are string connectors in Oracle, which is not disputed. So what does the second ' and third ' mean? The second ' is an escape character
The third ' is what we really are.

Method Two: The same is the use of escape characters, but only different ways

SQL > Select ' Test ' from dual;

Note: The second, third, here is the escape character and the real content that we mentioned in method one above

Method Three: In SQL will be ' replaced with Chr (39), because Chr (39) is ' the ASCII code

SQL > Select ' It ' | | Chr (39) | | ' Fine ' from dual;