Processing of special Oracle symbols: setdefineoff

Oracle special symbol processing, today encountered a problem, colleagues using dynamic SQL generated insert statement, which contains symbols, when doing insert, this symbol is used as

Oracle special symbol processing, today encountered a problem, colleagues using dynamic SQL generated insert statement, which contains symbols, when doing insert, this symbol is used as

Oracle special symbol & processing, today encountered a problem, the colleague uses the insert statement generated by dynamic SQL, which contains the & symbol, during the insert, this & symbol is used as a variable prompt. How can this problem be solved? It's actually quite simple: set define off

SQL> create tablet (avarchar2 (20 ));

Table created.

SQL> insertinto tvalues ('aaa & bbb ');
Enter valuefor bbb :.
Old1: insert into T values ('aaa & bbb ')
New1: insert into T values ('aaa .')

1 rowcreated.

SQL> rollback;

Rollback complete.

SQL>
SQL> -- wenow setdefine toOFF, theninsert data.
SQL> setdefine off
SQL> insertinto tvalues ('aaa & bbb ');

1 rowcreated.

SQL> select * from t;

A
--------------------
Aaa & bbb

SQL>

We found that the data with the & symbol has been inserted after set define off.

Related reading:

Handling of special Oracle symbols '&'