Lab 10.3 Exception Propagation

Lab Objectives

After this Lab, you will be able to:

Understand How Exceptions Propagate

Re-raise Exceptions

You already have seen how different types of exceptions are raised when a runtime error occurs in the executable portion of the PL/SQL block. However, a runtime error may occur in the declaration section of the block or in the exception-handling section of the block. The rules that govern how exceptions are raised in these situations are referred to as exception propagation.

Consider the first case : A runtime error occurred in the executable section of the PL/SQL block. This case should be treated as a review because the examples that you have seen earlier in this chapter show how an exception is raised when an error occurs in the executable section of the block.

If there is an exception specified associated with a particular error, control is passed to the exception-handling section of the block. Once the statements associated with the exception are executed, control is passed to the host environment or to the enclosing block. If there is no exception handler for this error, the exception is propagated to the enclosing block (outer block). Then the steps just described are repeated again. If no exception handler is found, the execution of the program halts, and control is transferred to the host environment.

Next, take a look at a second case : A runtime error occurred in the declaration section of the block. If there is no outer block, the execution of the program halts, and control is passed to the host environment. Consider the following script.

FOR EXAMPLE

 DECLARE    v_test_var CHAR(3):= 'ABCDE'; BEGIN    DBMS_OUTPUT.PUT_LINE ('This is a test'); EXCEPTION    WHEN INVALID_NUMBER OR VALUE_ERROR THEN       DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; 

When executed, this example produces the output shown:

  DECLARE   *   ERROR at line 1:   ORA-06502: PL/SQL: numeric or value error: character string buffer too small   ORA-06512: at line 2  

As you can see, the assignment statement in the declaration section of the block causes an error. Even though there is an exception handler for this error, the block is not able to execute successfully. Based on this example you may conclude that when a runtime error occurs in the declaration section of the PL/SQL block, the exception-handling section of this block is not able to catch the error.

Next, consider an example with nested PL/SQL blocks.

FOR EXAMPLE

  --outer block   BEGIN  -- inner block    DECLARE       v_test_var CHAR(3):= 'ABCDE';    BEGIN       DBMS_OUTPUT.PUT_LINE ('This is a test');    EXCEPTION       WHEN INVALID_NUMBER OR VALUE_ERROR THEN          DBMS_OUTPUT.PUT_LINE ('An error has occurred in '             'the inner block');    END;  EXCEPTION   WHEN INVALID_NUMBER OR VALUE_ERROR THEN   DBMS_OUTPUT.PUT_LINE ('An error has occurred in the '   'program');  END; 

When executed, this example produces the output shown:

  An error has occurred in the program   PL/SQL procedure successfully completed.  

In this example, the PL/SQL block is enclosed by another block, and the program is able to complete. This is possible because the exception defined in the outer block is raised when the error occurs in the declaration section of the inner block. Therefore, you can conclude that when a runtime error occurs in the declaration section of the inner block, the exception immediately propagates to the enclosing (outer) block .

Finally, consider a third case: A runtime error occurred in the exception-handling section of the block. Just like in the previous case, if there is no outer block, the execution of the program halts, and control is passed to the host environment. Consider the following script.

FOR EXAMPLE

 DECLARE    v_test_var CHAR(3) := 'ABC'; BEGIN    v_test_var := '1234';    DBMS_OUTPUT.PUT_LINE ('v_test_var: 'v_test_var); EXCEPTION    WHEN INVALID_NUMBER OR VALUE_ERROR THEN       v_test_var := 'ABCD';       DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; 

When executed, this example produces the output shown:

  DECLARE   *   ERROR at line 1:   ORA-06502: PL/SQL: numeric or value error: character string buffer too small   ORA-06512: at line 8   ORA-06502: PL/SQL: numeric or value error: character string buffer too small  

As you can see, the assignment statement in the executable section of the block causes an error. Therefore, control is transferred to the exception-handling section of the block. However, the assignment statement in the exception-handling section of the block raises the same error. As a result, the output of this example contains the same error message twice. The first message is generated by the assignment statement in the executable section of the block, and the second message is generated by the assignment statement of the exception-handling section of this block. Based on this example, you may conclude that when a runtime error occurs in the exception-handling section of the PL/SQL block, the exception-handling section of this block is not able to prevent the error.

Next, consider an example with nested PL/SQL blocks.

FOR EXAMPLE

  --outer block   BEGIN  -- inner block    DECLARE       v_test_var CHAR(3) := 'ABC';    BEGIN       v_test_var := '1234';       DBMS_OUTPUT.PUT_LINE ('v_test_var: 'v_test_var);    EXCEPTION       WHEN INVALID_NUMBER OR VALUE_ERROR THEN          v_test_var := 'ABCD';          DBMS_OUTPUT.PUT_LINE ('An error has occurred in '             'the inner block');    END;  EXCEPTION   WHEN INVALID_NUMBER OR VALUE_ERROR THEN   DBMS_OUTPUT.PUT_LINE ('An error has occurred in the '   'program');   END;  

When executed, this example produces the output shown:

  An error has occurred in the program   PL/SQL procedure successfully completed.  

In this example, the PL/SQL block is enclosed by another block, and the program is able to complete. This is possible because the exception defined in the outer block is raised when the error occurs in the exception-handling section of the inner block. Therefore, you can conclude that when a runtime error occurs in the exception-handling section of the inner block, the exception immediately propagates to the enclosing block.

In the previous two examples, an exception is raised implicitly by a runtime error in the exception-handling section of the block. However, an exception can be raised in the exception-handling section of the block explicitly by the RAISE statement. Consider the following example.

FOR EXAMPLE

 --outer block DECLARE    e_exception1 EXCEPTION;    e_exception2 EXCEPTION; BEGIN    -- inner block    BEGIN       RAISE e_exception1;    EXCEPTION       WHEN e_exception1 THEN          RAISE e_exception2;       WHEN e_exception2 THEN          DBMS_OUTPUT.PUT_LINE ('An error has occurred in '             'the inner block');    END; EXCEPTION    WHEN e_exception2 THEN       DBMS_OUTPUT.PUT_LINE ('An error has occurred in '          'the program'); END; 

This example produces the output shown:

  An error has occurred in the program   PL/SQL procedure successfully completed.  

Here two exceptions are declared : e_exception1 and e_exception2 . Exception e_exception1 is raised in the inner block via the statement RAISE. In the exception-handling section of the block, exception e_exception1 tries to raise e_exception2 . Even though there is an exception handler for the exception e_exception2 in the inner block, control is transferred to the outer block. This happens because only one exception can be raised in the exception-handling section of the block. Only after one exception has been handled can another be raised, but two or more exceptions cannot be raised simultaneously .

When a PL/SQL block is not enclosed by another block, control is transferred to the host environment, and the program is not able to complete successfully. Then the following error message is displayed.

  DECLARE   *   ERROR at line 1:   ORA-06510: PL/SQL: unhandled user-defined exception   ORA-06512: at line 10   ORA-06510: PL/SQL: unhandled user-defined exception  

Re-Raising an Exception

On some occasions you may want to be able to stop your program if a certain type of error occurs. In other words, you may want to handle an exception in the inner block and then pass it to the outer block. This process is called re-raising an exception. The following example helps to illustrate this point.

FOR EXAMPLE

 -- outer block DECLARE    e_exception EXCEPTION; BEGIN    -- inner block    BEGIN       RAISE e_exception;    EXCEPTION       WHEN e_exception THEN          RAISE;    END; EXCEPTION    WHEN e_exception THEN       DBMS_OUTPUT.PUT_LINE ('An error has occurred'); END; 

In this example, the exception e_exception is declared in the outer block. Then it is raised in the inner block. As a result, control is transferred to the exception-handling section of the inner block. The statement RAISE in the exception-handling section of the block causes the exception to propagate to the exception-handling section of the outer block. Notice that when the RAISE statement is used in the exception-handling section of the inner block, it is not followed by the exception name .

When run, this example produces the output shown:

  The error has occurred   PL/SQL procedure successfully completed.  

It is important to note that when an exception is re-raised in the block that is not enclosed by any other block, the program is unable to complete successfully. Consider the following example:

 DECLARE    e_exception EXCEPTION; BEGIN    RAISE e_exception; EXCEPTION    WHEN e_exception THEN       RAISE; END; 

When run, this example produces the following output:

  DECLARE   *   ERROR at line 1:   ORA-06510: PL/SQL: unhandled user-defined exception   ORA-06512: at line 7