I hardly ever use the quote delimiter in Oracle. This is actually quite useful though if you have a lot of special characters and single-quotes to work with in your SQL statements. (You don’t have to use the dreaded ” to get a ‘ every time!)

To use the quote delimiter (Syntax: q’<delim> Text you want <delim>’) in Oracle you can do the following:

– To ignore &, set define off
set define off

– The use the quote delimiter

insert into x(col1, col2) values (‘abc’, q’#This’s is how we do it & yes it’s cool!#’);

NOTE: The pound “#” character is the delimiter in this example but you can use whatever you like as long as it is not in the string.

Here is an Example to better illustrate how it is used:

SQL> create table x(col1  varchar2(10), col2 varchar2(128));

Table created.

SQL> set  define off
SQL> insert into x(col1, col2)
    values ('abc', q'#This's is how  we do it & yes it's cool!#');

1 row created.

SQL> select *  from x;

COL1
COL2
abc
This's is how we do it & yes it's cool!

SQL>

Enjoy!