29 January 2012

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

3 comments:

  1. Well, isn't it so that EXECUTE IMMEDIATE only knows about schema level, what is declared inside and what is passed via IN or OUT?

    I have never tried it but would not expect RAISE to somehow circumvent that. plch_pkg is surely handled, but where would the output go unless you handle it in an internal exception handler and re-raise?

    DBMS_SQL becomes an integral part of the block and behaves as such.

    My 5 (tired) cents ;-)

    ReplyDelete
  2. If we add to plch_pkg:
    PRAGMA EXCEPTION_INIT(e2,-21234);
    then the exception is caught.

    We would probably need to see the code inside EXECUTE IMMEDIATE and DBMS_SQL to determine the difference.

    Something around user-defined vs. internal exceptions :-)

    Mike

    ReplyDelete
  3. Hello Mike, All,

    The behavior of DBMS_SQL in this case does indeed suggest that "it becomes an integral part of the block", but, however, I took Quiz of 24 Jan 2011, whose topic was exactly to demonstrate the "schema level scope" of EXECUTE IMMEDIATE and all the choices of that quiz can be reproduced for DBMS_SQL as well, with the same behavior as with EXECUTE IMMEDIATE.

    In fact, even if we use a STATIC pl/sql unit call inside a pl/sql block, and that code raises an uninitialized exception
    ( like plch_pkg.e2 ), I would still expect to encounter the same behavior as with dynamic sql, because when an exception is raised or reraised, all that propagates out (that is, to the enclosing/calling block) is the error code (which is always 1 for an unitialized exception) and error message (which here is always "User-Defined exception"), and, the different uninitialized user exceptions "seem to be all different from each other", otherwise, having two of them handled in separate exception handlers would have failed compilation.

    However, for the static pl/sql unit call, it behaves exactly as if the program unit call was a nested block, where we saw that the exception name raised is still "remembered", even if the exceptions were not initialized.

    It looks to me a little bit inconsistent that PL/SQL does not allow for two separate exception handlers for two exceptions that are initialized to the same error code, but does allow this for two uninitialized exceptions, that both have SQLCODE=1.

    Looks like uninitialized exceptions behave like kind of "objects", having separate "instances", so two of those are sometimes considered as separate objects, but other times not.

    On the other hand, initialized exceptions completely lose their "instance identity" once they are initialized to the same error code, and you cannot even compile a code with two separate handlers for them.

    The best lesson to learn from this is that we probably should never use uninitialized user-declared exceptions beyond the limits of a single block of code.


    Looks like a good open question for Bryn Llewellyn :) :)


    But, by the way, can anybody imagine himself/herself having known about this WITHOUT playing the PL/SQL Challenge ?

    Again, a huge thanks to Steven and the PL/SQL Challenge for our opportunity to have met them :) :)


    Thanks a lot & Best Regards,
    Iudith

    ReplyDelete