Oracle PL/SQL for DBAs

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. The syntax for an exception handler is as follows:

    WHEN exception_name [ OR exception_name... ]
    THENexecutable statements

or:

    WHEN OTHERS
    THEN
       executable statements

The WHEN OTHERS clause is optional; if it is not present, then any unhandled exception is immediately propagated back to the enclosing block, if any. The WHEN OTHERS clause must be the last exception handler in the exception section.

Built-in error functions

Oracle provides several built-in functions to help you identify, analyze, and respond to errors that occur in your PL/SQL application.

SQLCODE

SQLCODE returns the error code of the most recently raised exception in your block. If there is no error, SQLCODE returns 0. SQLCODE also returns 0 when you call it outside of an exception handler.

SQLERRM

SQLERRM is a function that returns the error message for a particular error code. If you do not pass an ...