I have been playing the PL/SQL Challenge quiz every day. It’s a great way to brush up on my PL/SQL skills. After many years of using PL/SQL, I am still learning about features that I have never known before or totally forgotten about. Take for example one of last week’s quiz questions:
What is the output of this PL/SQL block?
eddie@db11gr2> DECLARE 2 aname varchar2 (5); 3 BEGIN 4 DECLARE 5 aname varchar2 (5) := 'Big String'; 6 BEGIN 7 DBMS_OUTPUT.put_line (aname); 8 EXCEPTION 9 WHEN VALUE_ERROR 10 THEN 11 DBMS_OUTPUT.put_line ('Inner block'); 12 END; 13 DBMS_OUTPUT.put_line ('What error'); 14 EXCEPTION 15 WHEN VALUE_ERROR 16 THEN 17 DBMS_OUTPUT.put_line ('Outer block'); 18 END; 19 /
Instead of telling you what the answer is, I will point you to the documentation:
In declarations, faulty initialization expressions can raise exceptions, but exception handlers cannot handle them… Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block.
Even though I selected the wrong choice when I took the quiz, adding the above fact to my PL/SQL knowledge and sharing it with you is a big win for me.
By the way, should we consider initializing variables in the declaration section a bad practice?