Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL Exception Propagation

PL/SQL Exception Propagation

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

Introduction to the exception propagation

When an exception occurs, PL/SQL looks for an exception handler in the current block e.g., 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; /

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:

plsql exception propagation example 1

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:

plsql exception propagation example 2

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

In this case, both inner block and enclosing block has 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 host environment:

plsql exception propagation example 3

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

  • Was this tutorial helpful?
  • YesNo
Previous Oracle RAISE_APPLICATION_ERROR
Next Handling Other Unhandled Exceptions

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.