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?
If you execute,
show errors
after the call, you will see a message like this:Here is a simplified version of your call that works. I added the chr(10)
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.