PL/SQL Exceptions Propagation during Variable Declaration

It's always good to know how any language handles and propagates exceptions, Oracle PL/SQL being no different. They're plenty of examples online about raising and handling exceptions on the web, but one thing you may not have realized is how PL/SQL propagates exceptions that occur in the variable declaration section of a procedure.

In the first example I created a procedure that has a variable, l_var, which can handle one character. As expected, when I assign more then one character an exception is raised and is propagated to the EXCEPTION block of the procedure.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

SQL> CREATE OR REPLACE PROCEDURE sp_test(p_var in varchar2)   2  AS   3    l_var VARCHAR2(1);   4  BEGIN   5    dbms_output.put_line('***START***');   6    l_var := 'abc';   7  exception   8    WHEN others THEN   9      dbms_output.put_line('***Exception***');  10      raise;  11  END sp_test;  12  /   Procedure created.   SQL> exec sp_test(p_var => 'abc'); ***START*** ***Exception*** BEGIN sp_test(p_var => 'abc'); END;   * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "ODTUG.SP_TEST", line 10 ORA-06512: at line 1

In the next example, instead of assigning the value in the main block of code I assigned the value in the declaration section. You'll notice that the procedure doesn't even get to the "START" line nor is the exception handled in the procedure's exception block. Instead the exception is propagated to the calling process right away.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SQL> CREATE OR REPLACE PROCEDURE sp_test(p_var in varchar2)   2  AS   3    l_var VARCHAR2(1) := p_var;   4  BEGIN   5    dbms_output.put_line('***START***');   6  exception   7    WHEN others THEN   8      dbms_output.put_line('***Exception***');   9      raise;  10  END sp_test;  11  /   Procedure created.   SQL> exec sp_test(p_var => 'abc'); BEGIN sp_test(p_var => 'abc'); END;   * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "ODTUG.SP_TEST", line 3 ORA-06512: at line 1

Before you go and change any of your existing code based on this article, I'm not saying that you should avoid defining variables in the declaration section of a procedure. Instead, just be aware of how the exception is propagated. This can be useful to know if your local variable is assigned to an input parameter. In that case you may want to assign the local variable in the main block of code rather then in the variable declaration section.

For documentation of how PL/SQL propagates exceptions raised in declarations go here. If you haven't already done so, I'd recommend reading the entire PL/SQL Error Handling documentation.