7.7.4. WHEN …​ DO

Used for

Catching an exception and handling the error

Available in

PSQL

Syntax

WHEN {<error> [, <error> …] | ANY}
DO <compound_statement>
<error> ::=
  { EXCEPTION exception_name
  | SQLCODE number
  | GDSCODE errcode }

The WHEN …​ DO statement is used to handle errors and user-defined exceptions. The statement catches all errors and user-defined exceptions listed after the keyword WHEN keyword. If WHEN is followed by the keyword ANY, the statement catches any error or user-defined exception, even if they have already been handled in a WHEN block located higher up.

The WHEN …​ DO block must be located at the very end of a block of statements, before the block’s END statement.

The keyword DO is followed by a statement, or a block of statements inside a BEGIN …​ END block, that handle the exception. The SQLCODE, GDSCODE, and SQLSTATE context variables are available in the context of this statement or block. The EXCEPTION statement, without parameters, can also be used in this context to re-throw the error or exception.

Targeting GDSCODE

The argument for the WHEN GDSCODE clause is the symbolic name associated with the internally-defined exception, such as grant_obj_notfound for GDS error 335544551.

After the DO clause, another GDSCODE context variable, containing the numeric code, becomes available for use in the statement or the block of statements that code the error handler. That numeric code is required if you want to compare a GDSCODE exception with a targeted error.

The WHEN …​ DO statement or block is never executed unless one of the events targeted by its conditions occurs in run-time. If the statement is executed, even if it actually does nothing, execution will continue as if no error occurred: the error or user-defined exception neither terminates nor rolls back the operations of the trigger or stored procedure.

However, if the WHEN …​ DO statement or block does nothing to handle or resolve the error, the DML statement (SELECT, INSERT, UPDATE, DELETE, MERGE) that caused the error will be rolled back and none of the statements below it in the same block of statements are executed.

Scope of a WHEN …​ DO Statement

A WHEN …​ DO statement catches errors and exceptions in the current block of statements. It also catches similar exceptions in nested blocks, if those exceptions have not been handled in them.

All changes made before the statement that caused the error are visible to a WHEN …​ DO statement. However, if you try to log them in an autonomous transaction, those changes are unavailable, because the transaction where the changes took place is not committed at the point when the autonomous transaction is started. Example 4, below, demonstrates this behaviour.

Examples using WHEN…​DO

1. Replacing the standard error with a custom one:

   CREATE EXCEPTION COUNTRY_EXIST '';
   SET TERM ^;
   CREATE PROCEDURE ADD_COUNTRY (
     ACountryName COUNTRYNAME,
     ACurrency VARCHAR(10) )
   AS
   BEGIN
     INSERT INTO country (country, currency)
     VALUES (:ACountryName, :ACurrency);
     WHEN SQLCODE -803 DO
       EXCEPTION COUNTRY_EXIST 'Country already exists!';
   END^
   SET TERM ^;

2. Logging an error and re-throwing it in the WHEN block:

   CREATE PROCEDURE ADD_COUNTRY (
     ACountryName COUNTRYNAME,
     ACurrency VARCHAR(10) )
   AS
   BEGIN
     INSERT INTO country (country,
                          currency)
     VALUES (:ACountryName,
             :ACurrency);
     WHEN ANY DO
     BEGIN
       -- write an error in log
       IN AUTONOMOUS TRANSACTION DO
         INSERT INTO ERROR_LOG (PSQL_MODULE,
                                GDS_CODE,
                                SQL_CODE,
                                SQL_STATE)
         VALUES ('ADD_COUNTRY',
                 GDSCODE,
                 SQLCODE,
                 SQLSTATE);
       -- Re-throw exception
       EXCEPTION;
     END
   END

3. Handling several errors in one WHEN block

   ...
   WHEN GDSCODE GRANT_OBJ_NOTFOUND,
        GDSCODE GRANT_FLD_NOTFOUND,
        GDSCODE GRANT_NOPRIV,
        GDSCODE GRANT_NOPRIV_ON_BASE
   DO
   BEGIN
     EXECUTE PROCEDURE LOG_GRANT_ERROR(GDSCODE);
     EXIT;
   END
   ...

See also

EXCEPTION, CREATE EXCEPTION, SQLCODE and GDSCODE Error Codes and Message Texts and SQLSTATE Codes and Message Texts