when we execute the SQL Show all command under Sql*plus, we can find a parameter: Define & (Hex 26), as shown below
Concat. (Hex 2e)
Copycommit 0
Copytypecheck on
Define & (Hex 26)
Describe DEPTH 1 linenum off INDENT offecho off
1, "&" EscapeThis 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!
2, "'" Escape• Method One: Use escape characters
SQL Select ' Test ' | | "From dual;
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
Oracle special character escapes:& and '
