The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

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.

3 comments:

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?

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 :) :)