How Exceptions Propagate in PL/SQL

An exception does not have to be handled where it is raised. It travels outward, through every enclosing block and calling procedure, until it finds a handler, or reaches the caller as an error. Knowing how it travels, including the special case of errors in declarations, and how to log and re-raise without losing information, is the core of reliable PL/SQL error handling.

Code for This Guide

The main examples are in the examples/exceptions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

The Rules

1. When an exception is raised, the rest of the current block is skipped and its EXCEPTION section is searched for a matching handler.
2. If there is none, the exception propagates to the enclosing block, or to the caller of a subprogram.
3. A handler for a different exception does not stop it; WHEN OTHERS catches anything.
4. Once handled, the exception is gone and execution continues after the block that handled it.

Through a Procedure and an Inner Block

Example:

declare
  procedure check_fare (p_fare number) is
  begin
    if p_fare < 0 then
      raise value_error;
    end if;
  end;
begin
  begin
    check_fare(-5);
    dbms_output.put_line('not reached');
  exception
    when no_data_found then dbms_output.put_line('inner handler');
  end;
exception
  when value_error then
    dbms_output.put_line('outer block caught: ' || sqlerrm);
end;
/

Output:

outer block caught: ORA-06502: PL/SQL: value or conversion error

PL/SQL procedure successfully completed.

The procedure raises VALUE_ERROR. The inner block handles only NO_DATA_FOUND, so the exception passes through it, and the outer block's VALUE_ERROR handler catches it. The line after the call is never reached.

Errors in Declarations

An exception raised while a block's declarations are elaborated, such as an initial value too long for its variable, happens before the block's own handlers are active. Only an enclosing block can catch it.

Example:

begin
  declare
    v_code char(2) := 'DXB';        -- too long: raised when the block starts
  begin
    dbms_output.put_line(v_code);
  exception
    when value_error then dbms_output.put_line('inner handler never sees it');
  end;
exception
  when value_error then
    dbms_output.put_line('caught by the enclosing block: ' || sqlerrm);
end;
/

Output:

caught by the enclosing block: ORA-06502: PL/SQL: value or conversion error: character string buffer too small

PL/SQL procedure successfully completed.

Log and Re-Raise

A WHEN OTHERS handler that swallows every error hides bugs. The pattern that works logs the error with its backtrace, which shows the line where it was first raised, and then re-raises it with RAISE so the caller still sees it.

Example:

create or replace procedure load_fares is
  v_n number;
begin
  v_n := to_number('many');
end;
/
begin
  load_fares;
exception
  when others then
    dbms_output.put_line('Error:     ' || sqlerrm);
    dbms_output.put_line('Backtrace: ' || dbms_utility.format_error_backtrace);
    raise;                -- re-raise after logging
end;
/

Output:

Procedure LOAD_FARES compiled

Error:     ORA-06502: PL/SQL: value or conversion error: character to number conversion error
Backtrace: ORA-06512: at "NIMBUS.LOAD_FARES", line 4
ORA-06512: at line 2

begin
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error: character to number conversion error
ORA-06512: at line 7
ORA-06512: at "NIMBUS.LOAD_FARES", line 4
ORA-06512: at line 2

The backtrace points to line 4 of LOAD_FARES, which SQLERRM alone does not. After the re-raise, SQLcl shows the error with the full stack. The example drops the procedure afterward.

Things to Know

• A handled exception does not roll back anything. An unhandled one that reaches the client undoes the work of the failed call, since the whole block or procedure call counts as one statement, but not earlier statements of the transaction.
• The compiler warning PLW-06009 flags WHEN OTHERS handlers that neither re-raise nor call RAISE_APPLICATION_ERROR.
• UTL_CALL_STACK gives the same information as the backtrace, item by item.

Related Guides

• How to Name Oracle Errors with PRAGMA EXCEPTION_INIT
• Error Handling with Dbms_utility.format_error_backtrace

Conclusion

PL/SQL exceptions propagate outward through blocks and callers until a matching handler catches them; errors in declarations skip the block's own handlers. Handle what you can where it makes sense, and otherwise log the error with its backtrace and re-raise it.