在插入数据遇到&时,oracle会认为是个变量,要求输入值。
原因在于oracle将&定义为自定义变量。
SQL> show all
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
......
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
......
此时只要set define off,即可插入包含&的值。
Using the SET DEFINE OFF Command
To avoid the prompt in the preceding example by using the SET DEFINE OFF
command, change the entry to the following:
SET DEFINE OFF CREATE TABLE "Employees & Managers" ( Employees varchar(16), Managers varchar(16));
SET DEFINE OFF
SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values.
注意:如果是在TOAD中执行,建议在每一个要导入的脚本第一行加上前面那句关闭define的话,否则当你导入第二个含有特殊字符的脚本的时候,又会出错。
如果是在SQL*PLUS中执行,则只需要设置一次define OFF,后面就可以连续导入了。直到你重新设置define ON为止。