How to insert & character or special character into Database using SQL*Plus.
By Jag - April 06, 2014
Solution 1:
If you are not using substitution variables (&1 &2 &3 etc.) you can do a “SET DEFINE OFF” or “SET SCAN OFF” to turn off the definition of the ampersand as a substitution variable.
Example:SQL> SET DEFINE OFFSQL> INSERT INTO <table_name> VALUES (‘AT&T’);1 row created
Solution 2:If you are using substitution variables, set the substitution character to one which will not be encountered when inserting data.
Example:SQL> SET DEFINE %SQL> INSERT INTO <temp_table> VALUES (‘AT&T’)/1 row created.
Solution 3:If you precede the special character with a backslash and the ESCAPE setting in SQL*Plus is set to ON, then SQL*Plus will understand the special character following the escape symbol is to be treated as a regular character.Example:SQL>set escape onSQL>show escapeescape “\” (hex 5c)SQL> INSERT INTO temp_table VALUES (‘select * from emp where ename = \&1′);1 row created.
0 comments