Oracle PL/SQL Programming, Third Edition

Handling Exceptions

Once an exception is raised, the current PL/SQL block stops its regular execution and transfers control to the exception section. The exception is then either handled by an exception handler in the current PL/SQL block or passed to the enclosing block.

To handle or trap an exception once it is raised, you must write an exception handler for that exception. In your code, your exception handlers must appear after all the executable statements in your program but before the END statement of the block. The EXCEPTION keyword indicates the start of the exception section and the individual exception handlers:

DECLARE
   ... declarations ...
BEGIN
   ... executable statements ...
[ EXCEPTION
   ... exception handlers ... ]
END;

The syntax for an exception handler is as follows:

WHEN exception_name [ OR exception_name ... ]
THEN
      executable statements

or:

WHEN OTHERS
THEN
   executable statements

You can have multiple exception handlers in a single exception section. The exception handlers are structured much like a conditional CASE statement, as shown in the following table:

Exception section	English-like translation
EXCEPTION WHEN NO_DATA_FOUND THEN executable_statements1;	If the NO_DATA_FOUND exception is raised, then execute the first set of statements.
WHEN payment_overdue THEN executable_statements2;	If the payment is overdue, then execute the second set of statements.
WHEN OTHERS THEN executable_statements3; END;	If any other exception is encountered, then execute ...