Oracle Special characters & Processing

today, when importing a batch of data to Oracle, there is a problem: Toad Tip To assign a custom variable amp, I was wondering at first that the data is a series of INSERT statements, how can there be a custom variable? Then I searched for the keyword amp discovery, originally because there is a field in the insert data that reads as follows:

http://mobile.three.com.hk/3DX?uid=0676&sid=rt_060908

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; 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 true with escape characters, except in a different way.

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;

Oracle special characters & processing