Exception handling (PL/SQL)

By default, any error encountered in a PL/SQL program stops execution of the program. You can trap and recover from errors by using an EXCEPTION section.

The syntax for exception handlers is an extension of the syntax for a BEGIN block.

Syntax

If no error occurs, the block simply executes statement, and control passes to the statement after END. However, if an error occurs while executing a statement, further processing of the statement is abandoned, and control passes to the EXCEPTION section. The WHEN clauses are searched for the first exception matching the error that occurred. If a match is found, the corresponding handler-statement is executed, and control passes to the statement after END. If no match is found, the program stops executing.

If a new error occurs during execution of the handler-statement, it can only be caught by a surrounding EXCEPTION clause.

Exceptions in a WHEN clause can be either user-defined or built-in. User-defined exceptions can be defined in the DECLARE section of either the current block or its surrounding block, or in the DECLARE section of a PL/SQL package. The syntax PRAGMA EXCEPTION_INIT or PRAGMA DB2_EXCEPTION_INIT can be used immediately after the definition of an exception, specifying the sqlcode or sqlstate that corresponds to the user-defined exception.

In the following example, the DECLARE section contains the definitions of three named exceptions. The body of the block is a call to procedure MyApp.Main. The EXCEPTION section contains handlers for the three exceptions:

exception1 is not associated with an sqlcode or sqlstate .
exception2 is associated with sqlcode -942 (Undefined name).
exception3 is associated with sqlstate 42601 (syntax error).

DECLARE
  exception1 EXCEPTION;
  exception2 EXCEPTION;
  PRAGMA EXCEPTION_INIT(exception2,-942);
  exception3 EXCEPTION;
  PRAGMA DB2_EXCEPTION_INIT(exception3,'42601');
BEGIN
  MyApp.Main(100);
EXCEPTION
  WHEN exception1 THEN
      DBMS_OUTPUT.PUT_LINE('User-defined exception1 caught');
  WHEN exception2 THEN
      DBMS_OUTPUT.PUT_LINE('User-defined exception2 (Undefined name) caught');
  WHEN exception3 THEN
      DBMS_OUTPUT.PUT_LINE('User-defined exception3 (Syntax error) caught');
END

Note: A limited number of Oracle sqlcodes are accepted by the Db2® data server as arguments to PRAGMA EXCEPTION_INIT. Refer to Oracle-Db2 error mapping (PL/SQL) for the full list.

When an exception initialized with PRAGMA EXCEPTION_INIT is caught, the value returned by the SQLCODE function is the sqlcode associated with the exception, not the Oracle value. In the previous example, when exception2 is caught, the value returned by SQLCODE will be -204, which is the sqlcode corresponding to Oracle sqlcode -942. If the Oracle sqlcode specified in PRAGMA EXCEPTION_INIT is not listed in the Oracle-Db2 error mapping table, then compilation fails. You can avoid this by replacing PRAGMA EXCEPTION_INIT with PRAGMA DB2_EXCEPTION_INIT and specifying the Db2 sqlstate corresponding to the error that you want identified.

Table 1 summarizes the built-in exceptions that you can use. The special exception name OTHERS matches every exception. Condition names are not case sensitive.

Table 1. Built-in exception names
Exception name	Description
CASE_NOT_FOUND	None of the cases in a CASE statement evaluates to "true", and there is no ELSE condition.
CURSOR_ALREADY_OPEN	An attempt was made to open a cursor that is already open.
DUP_VAL_ON_INDEX	There are duplicate values for the index key.
INVALID_CURSOR	An attempt was made to access an unopened cursor.
INVALID_NUMBER	The numeric value is invalid.
LOGIN_DENIED	The user name or password is invalid.
NO_DATA_FOUND	No rows satisfied the selection criteria.
NOT_LOGGED_ON	A database connection does not exist.
OTHERS	For any exception that has not been caught by a prior condition in the exception section.
SUBSCRIPT_BEYOND_COUNT	An array index is out of range or does not exist.
SUBSCRIPT_OUTSIDE_LIMIT	The data type of an array index expression is not assignable to the array index type.
TOO_MANY_ROWS	More than one row satisfied the selection criteria, but only one row is allowed to be returned.
VALUE_ERROR	The value is invalid.
ZERO_DIVIDE	Division by zero was attempted.