What Error

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?

Filed in Oracle, Tips on 20 Apr 10 | Tags: pl/sql

---