title: Is there a way to quote SQL strings containing apostrophes in Oracle to avoid needing to escape the apostrophe?
body: <p>I write a lot of dynamic SQL in PL/SQL of the kind:</p>
  
  <pre><code>-- 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'')';
  </code></pre>
  
  <p>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.</p>
  
  <p>(I asked this question here deliberately to document the quoting feature with my own answer)</p>