Oracle stores compilation wrapping routine with inline comments
I am trying to wrap a stored procedure in Oracle using the 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 so?
source to share
If you do, show errors
after the call, you will see the following message:
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's a simplified version of your call that works. I added 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's going on here? The pl / sql compiler takes your literal string and parses it. without a carriage return, your inline comment includes ALL of the text that follows it, and it is treated as a comment. Adding a carriage return character forces a newline and the parser is happy. So either use a "full" comment like / * blah .. * / or add carriage returns as needed. Or better yet, just use the "wrap" command line utility and get around it all.
source to share