Handling Exceptions

A block always terminates when PL/SQL raises an exception, but you can specify an exception handler to perform final actions before the block ends.

Methods for Raising an Exception

• An Oracle error occurs and the associated exception is raised automatically. For example, if the error ORA-01403 occurs when no rows are retrieved from the database in a SELECT statement, then PL/SQL raises the exception NO_DATA_FOUND. These errors are converted into predefined exceptions.
• Depending on the business functionality your program is implementing, you may have to explicitly raise an exception. You raise an exception explicitly by issuing the RAISE statement within the block. The exception being raised may be either userdefined or predefined.
• There are some nonpredefined Oracle errors. These errors are any standard Oracle errors that are not predefined. You can explicitly declare exceptions and associate them with the not predefined Oracle errors.

Syntax

EXCEPTION
  WHEN exception1 [OR exception2 . . .] THEN
    statement1;
    statement2; . . .

  [WHEN exception3 [OR exception4. . .] THEN
    statement3;
    statement4; . . .]
  [WHEN OTHERS THEN
    statement5;
    statement6; . . .]

Guidelines for Trapping Exceptions

• Begin the exception-handling section of the block with the EXCEPTION keyword.
• Define several exception handlers, each with its own set of actions, for the block.
• When an exception occurs, PL/SQL processes only one handler before leaving the block.
• Place the OTHERS clause after all other exception-handling clauses.
• You can have only one OTHERS clause.
• Exceptions cannot appear in assignment statements or SQL statements.