Tuesday, September 4, 2012

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.
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.
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.

3 comments:

  1. There was a good question on exception propagation and scope in Steven's PL/SQL Challenge recently, and /I've seen one related to declaration section exceptions in the past.

    ReplyDelete
  2. Martin,
    A question on error prorogation if I might ask...

    I have a page in an APEX 5 application. On this page is a before header process that fires when the page is requested with a specific REQUEST (DOWNLOAD_EXCEL). The code for this process is mainly a anonymous block:
    BEGIN

    Package.Procedure(Parameter1);

    EXCEPTION
    WHEN OTHERS THEN
    RAISE;

    END;

    Inside the packaged procedure is an exception block that does the same :

    EXCEPTION
    WHEN OTHERS THEN
    RAISE;

    I am having an issue with getting the APEX page to recognize an error has occurred and to actually display an error message or an alert..

    Any suggestions or ideas of where I can find a possible solution?

    Thank you,

    Tony Miller
    Los Alamos, NM

    ReplyDelete
    Replies
    1. Tony,

      In both cases I'd recommend getting rid of both "when others then raise" blocks. They provide no additional value to the code other than clutter it. Because their is a "raise" statement in there the exception handling will behave exactly as you normally expect.

      As for getting APEX to recognize that an error has occurred, do you use the custom APEX error handling function? This may help.

      Martin

      Delete