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:
"Inner block then 0 then What error?"
and so on. Well, the answer is:
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.