Oracle stored procedure wrapping compile error with inline comments

Question

I am trying to wrap a stored procedure in Oracle using dbms_ddl.create_wrapped() method. If the stored procedure contains an inline comment I get ORA-24344: success with compilation error

Errors:

BEGIN
dbms_ddl.create_wrapped('CREATE OR REPLACE PROCEDURE TEST_WRAP '
|| '('
|| '  NAME IN VARCHAR2 ' 
|| ') AS '
|| ' theName user_errors.name%TYPE; '
|| ' -- Inline Comment'
|| ' BEGIN '
|| ' BEGIN '
||  '  SELECT Name INTO theName FROM user_errors WHERE name LIKE ''Nothing''; '
||  ' EXCEPTION '
|| '   WHEN NO_DATA_FOUND THEN  '
||  '    NULL; '
|| ' END; '
|| ' EXCEPTION ' 
|| ' WHEN OTHERS THEN '
|| '   NULL; '
|| ' END TEST_WRAP; ');
END;

Works:

BEGIN
dbms_ddl.create_wrapped('CREATE OR REPLACE PROCEDURE TEST_WRAP '
|| '('
|| '  NAME IN VARCHAR2 ' 
|| ') AS '
|| ' theName user_errors.name%TYPE; '
|| ' BEGIN '
|| ' BEGIN '
||  '  SELECT Name INTO theName FROM user_errors WHERE name LIKE ''Nothing''; '
||  ' EXCEPTION '
|| '   WHEN NO_DATA_FOUND THEN  '
||  '    NULL; '
|| ' END; '
|| ' EXCEPTION ' 
|| ' WHEN OTHERS THEN '
|| '   NULL; '
|| ' END TEST_WRAP; ');
END;

Why is this the case?

OldProgrammer · Accepted Answer · 2015-06-08T01:51:50.533

If you execute, show errors after the call, you will see a message like this:

1/70           PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   begin function pragma procedure subtype type <an identifier>
   <a double-quoted delimited-identifier> current cursor delete
   exists prior

Here is a simplified version of your call that works. I added the chr(10)

  BEGIN
dbms_ddl.create_wrapped('CREATE OR REPLACE PROCEDURE TEST_WRAP '
|| '('
|| '  NAME IN VARCHAR2 ' 
|| ') AS '
|| ' theName varchar2(100); '
|| ' -- Inline Comment  ' || chr(10)
|| ' BEGIN '
|| '   NULL; '
|| ' END TEST_WRAP; ');
END;

So what is going on here? The pl/sql compiler is taking your literal string and parsing it. with no carriage return, your inline comment is including ALL the text that is following it and it is being treated as a comment. Adding the carriage return character forces a new line, and the parser is happy. So either use a "full" comment such as /* blah.. */, or add carriage returns as needed. Or better yet, just use the "wrap" command line utility and dispense with all of this.

one of many reasons I prefer multiline string as a single multiline literal over multiple concatenated strings, as in OP's question — Kirill Leontev, Jun 07 '15 at 22:50
The alternative quoting mechanism would also help here. Create the string like this to avoid having to escape quotation marks: ` q'< No need to escape ' here >'` — Jon Heller, Jun 08 '15 at 01:15

Oracle stored procedure wrapping compile error with inline comments

1 Answers1