Re: String literals in PL/SQL

From: Michael Young <michael.young_at_paetec.com>
Date: 12 Sep 2003 10:37:07 -0700
Message-ID: <83f9daa0.0309120937.16d2c5c8@posting.google.com>

Thanks for the feedback.

Be aware that the simple

   comment on table table_name is 'LINE 1' || CHR(10) || 'LINE2' ;

does NOT work. (If you can make it work - show me!)

The syntax of the comment statement requires a string literal - as soon as you put a string operator in the parameter, the result of the operator is a string variable, not a literal!

However, I can create a procedure :

CREATE OR REPLACE procedure ADD_TABLE_COMMENT (

   TABLE_NAME in varchar2,
   TABLE_COMMENT in varchar2 )
as
begin
execute immediate ( 'comment on table ' ||

                    TABLE_NAME          ||
		    ' is '''            ||
		    TABLE_COMMENT       ||
		    '''' ) ;

end ADD_TABLE_COMMENT ;

and then invoke it using (I'm using TOAD to execute the single statement, so 'call' works here...)

call ADD_TABLE_COMMENT( 'COMMENT_TEST',

                        ( 'LINE 1' || CHR(10) || 'LINE 2' ) ) ;

to get the desired effect - add a two-lined comment to the table COMMENT_TEST. When I select the comment from user_tab_comments, the client must be smart enough to know what to do with the LineFeed - in TOAD, I was getting the character displaying as "||" in query results, but it did show up as I wanted in the table comments using the db schema browser of that program.

Note that by using dynamic SQL, I can also solve my other issue - breaking up the comment string over multiple lines so its readily readable in the source script as well.

I wish I didn't have to resort to dynamic SQL, but it wasn't as painful as I originally thought, either. Received on Fri Sep 12 2003 - 12:37:07 CDT