title: Escaping single quote in PLSQL
body: <p>I want PLSQL to generate strings like:</p>
  
  <pre><code>COMMENT ON COLUMN TABLE.COLUMN IS 'comment from database';
  </code></pre>
  
  <p>My solution is:</p>
  
  <pre><code>declare
    str_comment varchar2(4000);
  begin
    for rec in (select table_name, column_name, description from description_table)
    loop
      str_comment:='COMMENT ON COLUMN '||rec.table_name||'.'||rec.column_name||'  IS '''||rec.description||'''; ' ;
      dbms_output.put_line(str_comment);
    end loop;
  end;
  </code></pre>
  
  <p>Output is OK when it doesn't contain single qoutes in <code>rec.description</code>. Otherwise there is need for escape letter. How should I implement it?</p>
  
  <p>OK output line (It's has escape letter to preserve single qoute):</p>
  
  <pre><code>COMMENT ON COLUMN TABLE1.COLUMN1_LV  IS 'It''s secret';
  </code></pre>
  
  <p>NOT NOK output line because no escape letter for single quote added and doesn't compile:</p>
  
  <pre><code>COMMENT ON COLUMN TABLE1.COLUMN1_LV  IS 'It's secret';
  </code></pre>
  
  <p>My solution is not to check if description contains single quotes. I just replace source (description) column's single quote by two single quotes before generating <code>COMMENT ON</code> strings and then I <code>ROLLBACK</code>.</p>
  
  <p>Any better solution?</p>