How to insert & character or special character into Database using SQL*Plus.

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 OFF
SQL> 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 on
SQL>show escape
escape “\” (hex 5c)
SQL> INSERT INTO temp_table VALUES (’select * from emp where ename = \&
1′);
1 row created.