Is there a way to quote SQL strings containing apostrophes in Oracle to avoid needing to escape the apostrophe?

Question

I write a lot of dynamic SQL in PL/SQL of the kind:

-- Let's assume the query is much more complex and bind variables are not a solution here
EXECUTE IMMEDIATE 'INSERT INTO foo (col) VALUES (''somevalue'')';

Observe the nasty escaping duplication of apostrophes to form valid SQL / PL/SQL syntax. Is there any way I can quote the SQL string literal such that I no longer have to escape? This would be great to copy paste the SQL string from an actual execution into a PL/SQL program, without tedious patching of apostrophes.

(I asked this question here deliberately to document the quoting feature with my own answer)

Answer 1

There is a little known feature since Oracle 10g called "quoting string literals". You can write:

EXECUTE IMMEDIATE q'[INSERT INTO foo (col) VALUES ('somevalue')]';

The price you pay is that your string literal can no longer contain ] because that's now a part of the string termination token. Possible characters to use in this way are:

• q'! ... !'
• q'[ ... ]'
• q'{ ... }'
• q'( ... )'
• q'< ... >'

More information:

• A blog post that explains it in more detail
• A livesql script by Steven Feuerstein illustrating it in action
• The official documentation
• An answer to this question on Stack Overflow