27

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?

mik
  • 3,575
  • 3
  • 20
  • 29
reforrer
  • 735
  • 7
  • 13
  • 18
  • Replacing the single quote with a double one is fine. What's the point of `ROLLBACK`? Could you please post the whole code (with replace and rollback)? – Quassnoi Jul 13 '11 at 12:04
  • @Quassnoi he probably updates the description_table, and then rollbacks the update – mik Jun 03 '16 at 12:55

4 Answers4

37

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,'''','''''')||'''; ' ;
Sodved
  • 8,428
  • 2
  • 31
  • 43
18

You can use the Quote operator like 

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

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

schurik
  • 7,798
  • 2
  • 23
  • 29
  • This will not work if the description itself contains `]'`. – mik Jun 03 '16 at 12:44
  • But you can use other characters in place of [ and ]. In fact, anything other than space, RETURN or tab. – DCookie Feb 02 '17 at 15:26
  • @mik I know this is an old comment, but for the benefit of anyone redirected to this answer, it's perfectly fine for the quoted string to contain the quotation character. For example, the following works perfectly well, at least in 11.2.0.4: `SELECT q'{{test}{from}}' FROM dual;` this returns: `{test}{from}` – Boneist Mar 07 '18 at 18:07
  • @Boneist, but, as I wrote, it cannot contain `}'` (quotation character followed by an apostrophe) – mik Mar 09 '18 at 14:49
  • @mik ah, good point. Clearly I need a new eyetest, as I somehow didn't see the `'`... d'oh! – Boneist Mar 09 '18 at 15:10
3

Use the REPLACE function in your select.

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;
dee-see
  • 23,668
  • 5
  • 58
  • 91
  • This will give `PLS-00302: component 'DESCRIPTION' must be declared`, because there is no `description` field in `rec`. You should either add a column alias after the `REPLACE()`, or put the `REPLACE` in the assignment to `str_comment` statement. – mik Dec 01 '20 at 12:11
-3

You need to use '' in the code But Before trying it to the actual code ,

try the line which has quotes in the dual

For example:

select '''sumesh''' from dual
Luke
  • 11,426
  • 43
  • 60
  • 69
Sumesh
  • 17