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.]
I present this little quiz in my classes on error management and the answers usually cover the logical range:
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.
Slight distraction that the wrong answer is "Big St" instead of "Big S".
ReplyDeleteNow, 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.)
Thanks, changed "Big St" to "Big S". Maybe I was thinking the space didn't count? "-)
ReplyDeletersiz, 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 */
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";
ReplyDeleteI love your blog, by the way.
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. :-)
ReplyDeleteAnd thanks for your kind words about my blog. Invite others to check it out, please!
ReplyDeleteNice Steven, I always find your article interesting and learning :)
ReplyDelete