PL/SQL Exception Propagation

Summary: in this tutorial, you will learn about how PL/SQL propagates an unhandled exception raised in the current block to the enclosing blocks.

Introduction to PL/SQL exception propagation

When an exception occurs, PL/SQL looks for an exception handler in the current block e.g., an anonymous block, procedure, or function of the exception. If it does not find a match, PL/SQL propagates the exception to the enclosing block of the current block.

PL/SQL then attempts to handle the exception by raising it once more in the enclosing block. This process continues in each successive enclosing block until there is no remaining block in which to raise the exception. If there is no exception handler in all blocks, PL/SQL returns an unhandled exception to the application environment that executed the outermost PL/SQL block.

Note that an unhandled exception stops the execution of the block.

Unhandled exception propagation examples

See the following anonymous block:

DECLARE
    e1 EXCEPTION;
    PRAGMA exception_init (e1, -20001);
    e2 EXCEPTION;
    PRAGMA exception_init (e2, -20002);
    e3 EXCEPTION;
    PRAGMA exception_init (e2, -20003);
    l_input NUMBER := &input_number;
BEGIN
    -- inner block
    BEGIN
        IF l_input = 1 THEN
            raise_application_error(-20001,'Exception: the input number is 1');
        ELSIF l_input = 2 THEN
            raise_application_error(-20002,'Exception: the input number is 2');
        ELSE
            raise_application_error(-20003,'Exception: the input number is not 1 or 2');
        END IF;
    -- exception handling of the inner block
    EXCEPTION
        WHEN e1 THEN 
            dbms_output.put_line('Handle exception when the input number is 1');
    END;
    -- exception handling of the outer block
    EXCEPTION 
        WHEN e2 THEN
            dbms_output.put_line('Handle exception when the input number is 2');
END;
/
Code language: SQL (Structured Query Language) (sql)

First, execute the block and enter 1 as the input number.

Because the input is 1, the inner block raises the e1 exception. The exception-handling part of the inner block handles the e1 exception locally, therefore, the execution of the block resumes in the enclosing block.

This picture illustrates the process:

Second, execute the block and enter 2 as the input number.

The inner block raises the e2 exception. Because the inner block does not have an exception handler to handle the e2 exception, PL/SQL propagates the e2 exception to the enclosing block.

The enclosing block has an exception handler that handles e2 exception. Therefore control passes to the host environment (SQL*Plus or SQL Developer).

The following picture illustrates the propagation of the unhandled exception from the inner block to its enclosing block:

Third, execute the block and enter 3 as the input number.

In this case, both the inner block and enclosing block have no exception handler to handle the e3 exception. Therefore, the block returns an unhandled exception to the host environment.

The following picture illustrates the propagation of the unhandled exception from the inner block to its enclosing block, and then the host environment:

In this tutorial, you have learned how PL/SQL propagates the exceptions from the current block to the enclosing blocks.