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!
