Using the q-operator for quoting strings

To quote a string containing quotes, like for example:

lv_statement := 'SELECT TO_DATE( ''12-12-2012'',''dd-mm-yyyy'') FROM DUAL';

one has to use double quotes to ‘escape’ the quotes in the string. Oracle (since 10gR2) has provided the q-operator, which enables this syntax::

lv_statement := q'[SELECT TO_DATE( '12-12-2012','dd-mm-yyyy') FROM DUAL]';

Brilliant! The q-operator causes everything between [ and ] to be interpreted as pure text, so no escaping of the quotes is necessary. [ and ] can be other characters as well, for example # or $. Make sure, however, that the string can never contain this character. If hook-like characters, like { or [ or ( or <, are used, the character at the end of the string has to be the matching one, so } or ] or ) or > in this example. Another example:

lv_statement := Q'#SELECT TO_DATE( '12-12-2012','dd-mm-yyyy') FROM DUAL#';

Note the use of de capital Q, the case of the q-operator makes no difference.

Quite handy when constructing dynamic SQL command strings!

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: