Skip to main content

Oh those tricky exception sections! Why won't it handle my error?

Fielded a question the other day from a frustrated PL/SQL developer. He included an exception handler for an exception that was being raised in his program, but the exception propagated out unhandled from the block anyway. What's with that?

To answer that question, why not test your own knowledge of PL/SQL: what will you see on the screen when you execute the following block (with server output enabled):

[Note, sure you could copy and paste the code and run it, but I urge you, I implore you, to just read the code and see if you can sort it out yourself. Reading code - verifying things logically - is a critical skill for any developer to, um, develop.]

DECLARE
   aname VARCHAR2(50);
BEGIN
   DECLARE
      aname VARCHAR2(5) := 'Big String';
   BEGIN
      DBMS_OUTPUT.PUT_LINE (aname);

   EXCEPTION
      WHEN VALUE_ERROR
      THEN
         DBMS_OUTPUT.PUT_LINE ('Inner block');
   END;
   
   DBMS_OUTPUT.put_line (SQLCODE);
   DBMS_OUTPUT.PUT_LINE ('What error?');
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.PUT_LINE ('Outer block');
END;

I present this little quiz in my classes on error management and the answers usually cover the logical range:

"Big S"
"Inner block"
"Inner block then 0 then What error?"

and so on. Well, the answer is:

Outer block

and nothing else. Why? Because in the world of PL/SQL, the exception section can only possibly handle an exception raised in the executable section. When it's raised in the declaration section, the exception always propagates out unhanded.

In this block of code, I try to stuff "Big String" into the nested a name variable - and it's just too long. PL/SQL does not assign the first five characters. Instead it raises the VALUE_ERROR exception (ORA-06502). Since this happened in the declaration section, that inner block's exception section never comes into play. 

The exception propagates out unhandled, shutting down the outer block's executable section, and control is transferred to the exception section, where a match is found and "Outer block" is displayed.


Comments

  1. Slight distraction that the wrong answer is "Big St" instead of "Big S".

    Now, a question, "Would it be an improvement to allow pre-amble EXCEPTION message definitions in PL/SQL in declaration blocks to localize the reported error?" If not, what would your recommendation be for verifying the plausible correctness of declaration blocks? (not rhetorical; I'm actually interested in how you do these things, my friend.)

    ReplyDelete
  2. Thanks, changed "Big St" to "Big S". Maybe I was thinking the space didn't count? "-)

    rsiz, I am a simple PL/SQL programmer. Could you explain perhaps with an example your "pre-amble EXCEPTION message definitions"?

    Also not entirely sure what you mean by plausible correctness, but generally the way I would approach this problem (I want to assign a default value to my variable!) is:

    1. If the default value is a literal, assign it in declaration section. Take responsibility for actually READING your code and verifying it is "plausible".

    2. If calling a function or executing an expression, move the assignment into a nested init procedure and call that at the start of your block. That way you can handle an exception inside the proc, and you do not clutter up your main exec section with assignments.

    BEGIN
    init;
    /* Now rest of block */

    ReplyDelete
  3. So my made-up name fails the "obviousness" test if it was unclear to you. What I mean is essentially directives that you could code up in the style of exceptions but which would fire at parse time instead of execution time so the machine does a bit of desk checking for you. Your suggestion #1 is good, but it would have leaked even for you on "Big St" as 5, and yes, I make trivial mistakes too. Suggestion #2 probably handles the machine automation check as well as implementing the putative feature would and is probably better style. For the example (which now seems moot), I guess allowing EXCEPTION BLOCKS immediately after the DECLARE line and before the actual declares would do it. So in your example the "Inner Block" exception block code would appear after the DECLARE line and before aname.... "Big String";

    I love your blog, by the way.

    ReplyDelete
  4. Oracle offers compile time warnings that will also flag "Big St" for us, as of 11.2 - PLW-6017. As for putting exception sections inside the declaration section, I am fairly confident in saying that is not going to happen in my lifetime. :-)

    ReplyDelete
  5. And thanks for your kind words about my blog. Invite others to check it out, please!

    ReplyDelete
  6. Nice Steven, I always find your article interesting and learning :)

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel