Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

B28370-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Exception Handler

An exception handler processes a raised exception (run-time error or warning condition). The exception can be either predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the run-time system. must be raised explicitly with either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR. The latter lets you associate an error message with the user-defined exception.

Syntax

exception_handler ::=

exception_handler
Description of the illustration exception_handler.gif

(statement ::=)

Keyword and Parameter Descriptions

exception_name

The name of either a predefined exception (such as ZERO_DIVIDE), or a user-defined exception previously declared within the current scope.

OTHERS

Stands for all the exceptions not explicitly named in the exception-handling part of the block. The use of OTHERS is optional and is allowed only as the last exception handler. You cannot include OTHERS in a list of exceptions following the keyword WHEN.

WHEN

Introduces an exception handler. You can have multiple exceptions execute the same sequence of statements by following the keyword WHEN with a list of the exceptions, separating them by the keyword OR. If any exception in the list is raised, the associated statements are executed.

Usage Notes

An exception declaration can appear only in the declarative part of a block, subprogram, or package. The scope rules for exceptions and variables are the same. But, unlike variables, exceptions cannot be passed as parameters to subprograms.

Some exceptions are predefined by PL/SQL. For a list of these exceptions, see Table 11-1. PL/SQL declares predefined exceptions globally in package STANDARD, so you need not declare them yourself.

Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration. In such cases, you must use dot notation to specify the predefined exception, as follows:

EXCEPTION
    WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN ...

The exception-handling part of a PL/SQL block is optional. Exception handlers must come at the end of the block. They are introduced by the keyword EXCEPTION. The exception-handling part of the block is terminated by the same keyword END that terminates the entire block. An exception handler can reference only those variables that the current block can reference.

Raise an exception only when an error occurs that makes it undesirable or impossible to continue processing. If there is no exception handler in the current block for a raised exception, the exception propagates according to the following rules:

Only one exception at a time can be active in the exception-handling part of a block. Therefore, if an exception is raised inside a handler, the block that encloses the current block is the first block searched to find a handler for the newly raised exception. From there on, the exception propagates normally.

Example


Example 1-12, "Using WHILE-LOOP for Control"
Example 1-16, "Creating a Standalone PL/SQL Procedure"
Example 2-28, "Block with Multiple and Duplicate Labels"
Example 5-35, "Using TRIM to Decrease the Size of a Collection"
Example 5-38, "Collection Exceptions"
Example 6-37, "Using ROLLBACK"
Example 7-13, "Using Validation Checks to Guard Against SQL Injection"
Example 8-1, "Declaring, Defining, and Invoking a Simple PL/SQL Procedure"
Example 10-3, "Creating the emp_admin Package"
Example 11-1, "Run-Time Error Handling"
Example 11-3, "Scope of PL/SQL Exceptions"
Example 11-9, "Reraising a PL/SQL Exception"
Example 12-6, "Using Rollbacks with FORALL"
Example 12-9, "Bulk Operation that Continues Despite Exceptions"

Related Topics