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

Q4 2011 Championship Playoff Results

You will find below the rankings for the Q4 2011 playoff; the number next to the player's name is the number of times that player has participated in a playoff. Congratulations first and foremost to our top-ranked players:

1st Place: Frank Schrader, Germany, wins an Amazon.com US$250 Gift Card.
2nd Place: Janis Baiza, Latvia, wins an Amazon.com US$175 Gift Card.
3rd Place: Valentin Nikotin, Russia, wins an Amazon.com US$100 Gift Card.

There are several results worthy of special comment:

1. Frank Schrader not only had the highest score, but actually got 100% of the quizzes right, the only player to do so. Very impressive, Frank! But even more impressive is that Frank won first place in the Q1 and Q3 2011 playoffs as well. In other words, Frank has placed 1st in 3 of 4 quarterly championships this year.

2. Valentin Nikotin pursued an interesting strategy. He completed the entire competition in just over 7 minutes, less than half that of almost all players. His % correct was "only" 86.2% (as compared to, say, that of Siim Kask with 96.6%, who placed just after him at 4th), which was enough to propel him to third place.

3. Vincent Malgrat participated in his first playoff, and broke into the top ten. Nice work, Vincent!

We have upgraded the Winners page to show you not only the rankings and results of all playoff participants (click on the All Playoff Prizes and Rankings button), but also make it easy for you to compare the players' performance in the playoff with that of the quarter.

Congratulations to everyone who played in the playoff. I hope you found it entertaining, challenging and educational.

Steven Feuerstein

Rank Name (# of Playoffs) Country Total Time Total Score
1Frank Schrader (6)Germany14 mins 22 secs2963
2Janis Baiza (4)Latvia22 mins 12 secs2641
3Valentin Nikotin (4)Russia07 mins 16 secs2635
4Siim Kask (5)Estonia24 mins 03 secs2619
5mentzel.iudith (5)Israel28 mins 44 secs2375
6Mike Pargeter (5)United Kingdom15 mins 08 secs2372
7Kevan Gelling (4)Isle of Man26 mins 55 secs2372
8Chris Saxon (3)United Kingdom15 mins 42 secs2356
9Jeff Kemp (7)Australia24 mins 33 secs2354
10Vincent Malgrat (1)French Republic23 mins 23 secs2277
11Niels Hecker (6)Germany25 mins 45 secs2250
12Randy Gettman (5)United States18 mins 39 secs2217
13Chad Lee (3)United States29 mins 39 secs2172
14John Hall (4)United States21 mins 01 secs2155
15james su (4)Canada22 mins 35 secs2123
16Joaquin Gonzalez (4)Spain14 mins 02 secs2094
17Dalibor Kovač (4)Croatia28 mins 10 secs2077
18Anna Onishchuk (4)Ireland27 mins 58 secs2056
19Andre van der Put (1)Netherlands12 mins 52 secs1998
20Viacheslav Stepanov (4)Russia19 mins 26 secs1971
21kowido (4)No Country Set29 mins 07 secs1943
22Stelios Vlasopoulos (2)Greece29 mins 54 secs1872
23Ninoslav Čerkez (2)Croatia29 mins 42 secs1871
24Frank Schmitt (1)Germany27 mins 38 secs1847
25Nina (1)Russia29 mins 24 secs1667
26Gideon Bruggink (1)Netherlands29 mins 56 secs1596
27Alain Boulianne (2)French Republic26 mins 40 secs1587
28_tiki_4_ (1)Germany25 mins 53 secs1377
29monpara.sanjay (1)India26 mins 28 secs1346
30Syed Ariful Bari (2)Bangladesh03 mins 20 secs1018