今天遇到一个问题，同事用动态sql生成的insert语句，其中含有&符号，在做insert时，这个&符号就被作为一个变量提示符了。如何解决这个问题？其实很简单：set define off
SQL> create table t (a varchar2(20));
Table created.
SQL> insert into t values('aaa&bbb');
Enter value for bbb: .
old 1: insert into t values('aaa&bbb')
new 1: insert into t values('aaa.')
1 row created.
SQL> rollback;
Rollback complete.
SQL>
SQL>--we now set define to OFF,then insert data.
SQL> set define off
SQL> insert into t values('aaa&bbb');
1 row created.
SQL> select * from t;
A
--------------------
aaa&bbb
SQL>
Table created.
SQL> insert into t values('aaa&bbb');
Enter value for bbb: .
old 1: insert into t values('aaa&bbb')
new 1: insert into t values('aaa.')
1 row created.
SQL> rollback;
Rollback complete.
SQL>
SQL>--we now set define to OFF,then insert data.
SQL> set define off
SQL> insert into t values('aaa&bbb');
1 row created.
SQL> select * from t;
A
--------------------
aaa&bbb
SQL>
我们发现set define off后，带&符号的数据已经插入。
