Single quote exception in PLSQL

Question

I want PLSQL to generate strings like:

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

My decision:

 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; 

The output is fine if it does not contain single qoutes in rec.description . Otherwise, there is a need for escape letter. How to implement it?

The output line is OK (to save one qoute has an escape letter):

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

The output string is NOT NOK, because no escape letter for a single quote is added or compiled:

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

My decision is not to check if the description contains single quotes. I simply replace the single quotation mark of the source column (description) with two single quotation marks before creating COMMENT ON lines, and then I ROLLBACK .

The best decision?

Answer 1

I make this stuff kind of honest (usually generating insert / update instructions).

You just need to use the replace function to turn everything ' into '' . those. change it to:

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

Answer 2

You can use the Quote statement, for example

 str_comment:='COMMENT ON COLUMN '||rec.table_name||'.'||rec.column_name||' IS q''[' ||rec.description|| ']'';' ; 

see http://psoug.org/reference/string_func.html

Answer 3

Use the REPLACE function in your selection.

 declare str_comment varchar2(4000); begin for rec in (SELECT table_name, column_name, REPLACE(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; 

Answer 4

You need to use '' in the code But before you try it in real code,

try the double quoted string

For example:

 select '''sumesh''' from dual