oracle - Escaping single quote in PLSQL

Question

I want PLSQL to generate strings like:

COMMENT ON COLUMN TABLE.COLUMN IS 'comment from database';

My solution is:

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;

Output is OK when it doesn't contain single qoutes in rec.description. Otherwise there is need for escape letter. How should I implement it?

OK output line (It's has escape letter to preserve single qoute):

COMMENT ON COLUMN TABLE1.COLUMN1_LV  IS 'It''s secret';

NOT NOK output line because no escape letter for single quote added and doesn't compile:

COMMENT ON COLUMN TABLE1.COLUMN1_LV  IS 'It's secret';

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 COMMENT ON strings and then I ROLLBACK.

Any better solution?

Answer 1

I do this sort stuff a fair bit (usually generating insert/update statements).

You just need to use the replace function to turn all the ' into ''. i.e. Change it to:

str_comment:='COMMENT ON COLUMN '||rec.table_name||'.'||rec.column_name
            ||' IS '''||REPLACE( rec.description,'''','''''')||'''; ' ;