Different error handling behavior between EXECUTE IMMEDIATE and DBMS_SQL (11296)

One of the Q4 2011 playoff quizzes examined the way that user-defined exceptions are handled. If you didn't participate in the playoff, you may want to view this quiz - even try to answer it for yourself - before reading this post.

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