ORACLE - Exceptions

Exceptions

An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers.

Syntax

Keyword and Parameter Description

WHEN

This keyword 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.

exception_name

This identifies a predefined exception such as ZERO_DIVIDE, or a user-defined exception previously declared within the current scope.

OTHERS

This keyword 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.

exception_name

This identifies a predefined exception such as ZERO_DIVIDE, or a user-defined exception previously declared within the current scope.

statement

This is an executable statement.

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.

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 should be raised only when an error occurs that makes it impossible or undesirable 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:

· If there is an enclosing block for the current block, the exception is passed on to that block. The enclosing block then becomes the current block. If a handler for the raised exception is not found, the process repeats.

· If there is no enclosing block for the current block, an unhandled exception error is passed back to the host environment.

However, exceptions cannot propagate across remote procedure calls (RPCs). Therefore, a PL/SQL block cannot catch an exception raised by a remote subprogram.

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.

An exception handler can reference only those variables that the current block can reference.

Example

The following PL/SQL block has two exception handlers:

DELARE

bad_emp_id EXCEPTION;

bad_acct_no EXCEPTION;

...

BEGIN

...

EXCEPTION

WHEN bad_emp_id OR bad_acct_no THEN -- user-defined

ROLLBACK;

WHEN ZERO_DIVIDE THEN -- predefined

INSERT INTO inventory VALUES (part_number, quantity);

COMMIT;

END;

How Exceptions Propagate

When an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. In the latter case, PL/SQL returns an unhandled exception error to the host environment.

However, exceptions cannot propagate across remote procedure calls (RPCs). Therefore, a PL/SQL block cannot catch an exception raised by a remote subprogram. For a workaround,

An exception can propagate beyond its scope, that is, beyond the block in which it was declared. Consider the following example:

BEGIN

...

DECLARE ---------- sub-block begins

past_due EXCEPTION;

BEGIN

...

IF ... THEN

RAISE past_due;

END IF;

END; ------------- sub-block ends

EXCEPTION

...

WHEN OTHERS THEN

ROLLBACK;

END;

Because the block in which it was declared has no handler for the exception named past_due, it propagates to the enclosing block. But, according to the scope rules, enclosing blocks cannot reference exceptions declared in a sub-block. So, only an OTHERS handler can catch the exception.