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