How to insert & in Oracle table example

I used to get so many calls from developers asking how to insert & into a varchar2 column in oracle?
It is not a difficult question to answer. There are many ways to insert & into a  table column in oracle database.

First of all, if you want to insert simply an &, then have you tried this?

SQL>CREATE TABLE my_table (col1 VARCHAR2(15));
SQL>INSERT INTO my_table VALUES ('&');
1 row created.

It will work perfectly!!. The problem arises when there is something written after &. For example,  When you try to insert the string  'SQL & JAVA' in oracle table, into a varchar2 column,You will get a message 'Enter value for JAVA:'
Do you know why?
Because the character & is set as the define character in oracle by default.
So it defines JAVA as a substitution variable and asks value to be substituted.
You can check it as given below.

SQL> show define
define "&" (hex 26)

Let us move into the topic of how to insert & in oracle table. You have different ways to insert & into oracle table. Here I am explaining them.

1. Put nothing after &

When there is something after &, oracle defines it as a substitution variable(because & means define), and will ask for entering a value for the variable when you execute the command.

Here, I need to insert the string 'SQL & JAVA', then how can I put nothing after &? how will I add 'JAVA' to the string?

 I will split the string into two, 'SQL &' and ' JAVA', and then I will concatenate them. as given in below example.

SQL>INSERT INTO my_table VALUES('SQL &' || ' JAVA');

1 row created.

2.Unset the define character

As I have shown above, & is the default define character. We can unset it and insert & in any oracle table.
Here is the example:

SQL> set define off

SQL>INSERT INTO my_table VALUES('SQL & JAVA');

1 row created.

SQL> set define &

The last line is added to set the define character back to the default settings after inserting the string including & into the desired column.

3. Use escape character

There is no default escape character in Oracle, So we have to define an escape character if you want to use one. You can define any character as an escape character. 

Here is the example of inserting & into an oracle table using escape character,

SQL> show escape

escape OFF

SQL> set escape \

SQL> show escape

escape "\" (hex 5c)

SQL>INSERT INTO my_table VALUES('SQL \& JAVA');

1 row created.

SQL> set escape off

4. Use ascii instead of &

This actually is an over head, but it will be helpful in some type of programs.
The ascii of & is (hex 26) or (dec 38). We can just check it out.

SQL> select chr(38) from dual;

C
-
& 

So in the statement 
INSERT INTO my_table VALUES('SQL & JAVA'); 
we will replace the & with, '||chr(38)||'
so the statement will be 
SQL>INSERT INTO my_table VALUES('SQL '||chr(38)||' JAVA'); 
1 row created.

-----------------------

Let us check whether all values in the table MY_TABLE is inserted correctly by selecting from the table.

SQL> select * from my_table;

COL1
---------------
&
SQL & JAVA
SQL & JAVA
SQL & JAVA
SQL & JAVA

So, The data were inserted correctly. We have learned how to insert & in oracle table.

If you have any other idea, Please post as comment.