Exception Propagation in PL/SQL

What is Exception Propagation in PL/SQL?

In PL/SQL, exception propagation refers to the process of passing an exception to an enclosing block when the current block does not handle it. This allows structured error handling in nested blocks and procedures.

Exceptions can be raised in three sections of a PL/SQL block:

Declarative Section
Executable Section
Exception Section

If an exception is raised but not handled within its own block, it is propagated to the enclosing block, following specific rules.

How Exception Propagation Works (Rules)

When an exception is raised, PL/SQL follows this propagation algorithm:

If the current block contains a handler for the exception, it executes the handler.
If the handler is not present, the exception is passed to the enclosing block.
If no block handles it, it is propagated to the calling environment, such as SQL*Plus or a client application.

Understanding Enclosing Blocks

An enclosing block is an outer block that wraps around an inner block or procedure. If an exception is unhandled in the inner block, it is propagated to this outer (enclosing) block.

Example:

BEGIN -- Outer block
  DECLARE -- Inner block
    ...
  BEGIN
    ...
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Inner block error');
  END;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Outer block error');
END;

Propagation Examples

Example 1: Exception Handled in Sub-block

BEGIN
  BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Error A');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Handled in sub-block');
  END;
  DBMS_OUTPUT.PUT_LINE('Continues in outer block');
END;

Output:

Handled in sub-block  
Continues in outer block

Example 2: Exception Propagated to Outer Block

BEGIN
  BEGIN
    RAISE_APPLICATION_ERROR(-20002, 'Error B');
  END;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Handled in outer block');
END;

Exceptions Raised in Declarative Section

If an error occurs during a declaration, the exception immediately propagates, skipping the current block’s handlers.

Example 4:

DECLARE
  v_number NUMBER(3) := 'ABC'; -- Raises VALUE_ERROR
BEGIN
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('This will not be printed');
END;

Example 5: Outer Block Catches Declarative Error

BEGIN
  DECLARE
    v_number NUMBER(3) := 'ABC';
  BEGIN
    NULL;
  END;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Outer block caught error');
END;

Exceptions in Exception Section

An exception can also be raised inside an exception handler, but PL/SQL allows only one active exception at a time. The new exception immediately propagates.

Example 6: Exception Raised Inside a Handler

BEGIN
  BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Error A');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Handling A');
      RAISE_APPLICATION_ERROR(-20002, 'Error B');
  END;
END;

Example 7: Re-Raising the Same Exception

BEGIN
  BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Error A');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Logging error');
      RAISE; -- Propagate same exception
  END;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Final handler in outer block');
END;

Best Practices for Exception Propagation

Always have an OTHERS handler at the outermost level to catch unhandled exceptions.
Use RAISE; to log and re-propagate the same error after cleanup or logging.
Avoid empty handlers like WHEN OTHERS THEN NULL; — they suppress important error information.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.