How to Insert and Update ampercent('&') character in the Oracle table

SQL> CREATE TABLE TEST ( TEST_ID  NUMBER,   TEST_DATA   VARCHAR2(50));
Table created.

To Insert or Update & into character field do the follow

SQL> show ESCAPE
escape OFF
SQL> Set ESCAPE "/"
SQL> show ESCAPE
escape "/" (hex 2f)

Now Insert Into the Test Table

SQL> INSERT INTO TEST (TEST_ID,TEST_DATA) VALUES (1,'Hospital /& Healthcare');

1 row created. 

Now Update Into the Test Table

SQL> UPDATE TEST
 2  Set TEST_DATA = 'Outsourcing /& Offshoring'
 3  WHERE TEST_ID = 1;

1 row updated.

Use the 10g Quoting mechanism:

Syntax q'[QUOTE_CHAR]Text[QUOTE_CHAR]'

N.B: Make sure that the QUOTE_CHAR doesnt exist in the text.

SELECT q'{This is Oracle’s 'quoted' text field & I like Oracle}' FROM DUAL;

SQL> INSERT INTO TEST (TEST_ID,TEST_DATA) VALUES (2,q'{ Aviation & Aerospace}');

1 row created.

SQL> UPDATE TEST
 2  Set TEST_DATA = q'{Information Technology & Services}'
 3  WHERE TEST_ID = 2;

1 row updated.

SQL> Select * from test;

 TEST_ID    TEST_DATA
---------- --------------------------------------------------
 1    Outsourcing & Offshoring
 2    Information Technology & Services