Iudith Mentzel, who placed 5th in the playoff, took a closer look at the handling of user-defined exceptions raised in a dynamic PL/SQL block - and discovered something odd: the behavior when native dynamic SQL (EXECUTE IMMEDIATE) was used is different from that of DBMS_SQL. Check it out....and let us know if you have an idea as to why this is happening.
1. Create a package with two user-defined exceptions.
CREATE OR REPLACE PACKAGE plch_pkg IS e1 EXCEPTION; e2 EXCEPTION; END; /2. Try to catch the exception with native dynamic SQL and it goes unhandled:
BEGIN
EXECUTE IMMEDIATE 'BEGIN RAISE plch_pkg.e2; END;';
EXCEPTION
WHEN plch_pkg.e1
THEN
DBMS_OUTPUT.put_line ('e1 caught');
WHEN plch_pkg.e2
THEN
DBMS_OUTPUT.put_line ('e2 caught');
END;
/
BEGIN
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 1
ORA-06512: at line 2
3. But with DBMS_SQL, it is trapped:DECLARE
c INTEGER := DBMS_SQL.open_cursor;
s INTEGER;
BEGIN
DBMS_OUTPUT.put_line ('Parsing ...');
DBMS_SQL.parse (c, 'BEGIN RAISE plch_pkg.e2; END;', DBMS_SQL.native);
DBMS_OUTPUT.put_line ('Executing ...');
s := DBMS_SQL.execute (c);
EXCEPTION
WHEN plch_pkg.e1
THEN
DBMS_OUTPUT.put_line ('e1 caught');
DBMS_OUTPUT.put_line ('status=' || s);
DBMS_OUTPUT.put_line ('sqlcode=' || SQLCODE);
WHEN plch_pkg.e2
THEN
DBMS_OUTPUT.put_line ('e2 caught');
DBMS_OUTPUT.put_line ('status=' || s);
DBMS_OUTPUT.put_line ('sqlcode=' || SQLCODE);
END;
/
Parsing ...
Executing ...
e2 caught
status=
sqlcode=1
This is very interesting and unexpected (to me). Do any of you have any ideas on what might be causing this?Thanks to Iudith for another fascinating exploration!
Cheers, SF
