Oracle 10g : Alternative Quoting Mechanism

If you are blessed and working on Oracle 10g, I believe this would be a useful tip.

Say we have a simple string to select : How's Ravi ?

Our select statement would be : select 'Hows Ravi?' from dual;

That was easy !!Say for example this is the string we are selecting. : 'Aah', it's 'raining'

This is how we usually escape it :

select Aah, its raining from dual;

Result : 'Aah', it's 'raining'

After praying God (Oracle), he gave us this cool feature, i.e, alternative quoting mechanism. :-)

select q'' from dual;

select q'<'Aah', it's raining'>' from dual;

The result is : 'Aah', it's raining'

Notice the new q'< ... > tag. Q or q before a text literal indicates that alternative quoting mechanism will be used.

You can change the opening and closing quote delimeters as shown below :

select q'{'Aah', it's raining'}' from dual; or select q'('Aah', it's raining')' from dual;

The good news is it works in pl/sql as well.

Note : If the opening opening quote delimeter is one of [, { , < or ( then the closing delimeter must be the corresponding ], }, > or )

In all other cases the opening and closing quote delimiter must be the same character.

Eg : select q'"'Aah', it's raining'"' from dual;

Hope this helps !!

Long live "Tom Kyte".

Good Luck,
r-a-v-i