Q-quote operator introduced in Oracle 10g

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

-- Document: Q-quoting mechanism

-- Created On: 17-Dec-07

-- Author: Amar Kumar Padhi

-- Email: amar.padhi@gmail.com

-- Purpose: Q-quote operator introduced in Oracle 10g.

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

 

We make use of single quotation mark in SQL and PL/SQL to identify sting literals. If the literal itself contains a single quote, we need to add one more quote next to it. This additional quote acts as an escape character and removes conflict with the outside quotes that are enclosing the string.

 

Oracle realises that long complex strings having lot of single quotes can turn out to become cumbersome and prone to errors that may not be caught during testing.

 

Release 10g onwards, a new quoting mechanism is provided in the form of "q". This new quote operator allows us to choose our own quotation mark delimiter.

 

Here are some examples -

SQL> select 'amar's web blog. It's personal..' str from dual;

select 'amar's web blog. It's personal..' str from dual

*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

 

What we normally do:-

SQL> select 'amar''s web blog. It''s personal..' str from dual;

STR

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

amar's web blog. It's personal..

1 row selected.

 

Here is use of Q - quote operator. The above statement can also be represented as any one of the below.

 

select q'(amar's web blog. It's personal.)' str from dual;

select q'[amar's web blog. It's personal.]' str from dual;

select q'Aamar's web blog. It's personal.A' str from dual;

select q'/amar's web blog. It's personal./' str from dual;

select q'Zamar's web blog. It's personal.Z' str from dual;

select q'|amar's web blog. It's personal.|' str from dual;

select q'+amar's web blog. It's personal.+' str from dual;

 

(New added)

select q'zamar's web blog. It's personal.z' str from dual;

 

And so on. After the Q-quote is specified, provide the single quote along with a unique character at the beginning of the string. To close the string, provide the same character followed by the single quote. The single and the provided character form the two character string enclosure.

 

If you decide to use braces, Oracle expects matching enclosures, i.e., Open brackets should be represented by closed brackets at the end of the string. Other characters can be represented as they are at both ends.

 

All we need to take care of is that the last two character delimiter does not show up in the string itself. For instance the below will throw error as the closing characters get repeated within the string.

 

SQL> select q'aamar's web blog. Ita's personal.a' str from dual;

ERROR:

ORA-01756: quoted string not properly terminated

 

The same can be used in PL/SQL also.

SQL> declare

2 l_str varchar2(100) := q'[amar's web blog. Ita's personal.]';

3 begin

4 dbms_output.put_line(l_str);

5 end;

6 /

amar's web blog. Ita's personal.

PL/SQL procedure successfully completed.

 

I recently came across a program that framed dynamic INSERT statement to copy data from one database to another. Unfortunately, one of the record columns had a single quote embedded in the string and this resulted in the program unit to fail in production. This happened in release 9i and the only alternative available was to remove/double the single quotes from the string literal. From release 10g, Q-quote could also be used to prevent this problem. I will simulate the same scenario and try this out.

 

SQL> create table am100(col1 number, col2 varchar2(100));

Table created.

SQL> create table am102(col1 number, col2 varchar2(100));

Table created.

SQL> insert into am100 values(1, q'[amar's web blog. It's personal]');

1 row created.

SQL> insert into am100 values(2, q'[this is a simple string]');

1 row created.

SQL> insert into am100 values(3, q'[this is just another string]');

1 row created.

SQL> select * from am100;

COL1 COL2

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

1 amar's web blog. It's personal

2 this is a simple string

3 this is just another string

3 rows selected.

SQL> commit;

Commit complete.

 

Now I will call a simple routine that will read the data and generate dynamic SQL for inserting into another table.

 

declare

l_str varchar2(4000);

begin

for rec in (select col1, col2 from am100) loop

l_str := 'insert into am102(col1, col2) values (' || rec.col1 ||',''' ||

rec.col2 || ''')';

dbms_output.put_line(l_str);

execute immediate l_str;

end loop;

end;

/

 

The unit errors out as below, because of the single quote mismatch.

insert into am102(col1, col2) values (1,'amar's web blog. It's personal');

begin

*

ERROR at line 1:

ORA-00917: missing comma

ORA-06512: at line 4

 

I will now modify the program unit to add Q-quote. We can also use the REPLACE function to remove or double-up single quote in strings, both options are given below.

 

[] With REPLACE function (doubling the single quote);

declare

l_str varchar2(4000);

begin

for rec in (select col1, replace(col2, '''', '''''') col2 from am100) loop

l_str := 'insert into am102(col1, col2) values ('

|| rec.col1 ||',''' || rec.col2 || ''')';

dbms_output.put_line(l_str);

execute immediate l_str;

end loop;

end;

/

 

Output generated:-

insert into am102(col1, col2) values (1,'amar''s web blog. It''s personal')

insert into am102(col1, col2) values (2,'this is a simple string')

insert into am102(col1, col2) values (3,'this is just another string')

 

[] With Q-quote

declare

l_str varchar2(4000);

Begin

for rec in (select col1, 'q''[' || col2 || ']''' col2 from am100) loop

l_str := 'insert into am102(col1, col2) values ('

|| rec.col1 ||',' || rec.col2 || ')';

dbms_output.put_line( l_str );

execute immediate l_str;

end loop;

end;

/

 

Output generated:-

insert into am102(col1, col2) values (1,q'[amar's web blog. It's personal]')

insert into am102(col1, col2) values (2,q'[this is a simple string]')

insert into am102(col1, col2) values (3,q'[this is just another string]')

 

Q-quote will be a handy option when we deal with huge text literals.