O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required