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,'''','''''')||'''; ' ;