Data Manipulation (DML) StatementsBuilt-in Scalar Functions

Scope of a `WHEN … DO` Statement

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

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.

Tip

When handling exceptions, it is sometimes desirable to handle the exception by writing a log message to mark the fault and having execution continue past the faulty record.Logs can be written to regular tables, but there is a problem with that: the log records will “disappear” if an unhandled error causes the module to stop executing, and a rollback is performed.Use of external tables can be useful here, as data written to them is transaction-independent.The date inserted into a linked external file will still be there, regardless of whether the overall process succeeds or not.

Examples using `WHEN…DO`

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 ^;

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,
                             MESSAGE)
      VALUES ('ADD_COUNTRY',
              GDSCODE,
              SQLCODE,
              SQLSTATE,
              RDB$ERROR(MESSAGE));
    -- Re-throw exception
    EXCEPTION;
  END
END

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,
    RDB$ERROR(MESSAGE);
  EXIT;
END
...

Catching errors using the SQLSTATE code

EXECUTE BLOCK
AS
  DECLARE VARIABLE I INT;
BEGIN
  BEGIN
    I = 1/0;
    WHEN SQLSTATE '22003' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Numeric value out of range.';
    WHEN SQLSTATE '22012' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Division by zero.';
    WHEN SQLSTATE '23000' DO
      EXCEPTION E_CUSTOM_EXCEPTION
       'Integrity constraint violation.';
  END
END

Scope Of A WHEN DO Statement

Scope of a `WHEN … DO` Statement

Examples using `WHEN…DO`

docnext count = 0

Scope Of A WHEN DO Statement

Scope of a WHEN …​ DO Statement

Examples using WHEN…​DO

docnext count = 0

Scope of a `WHEN … DO` Statement

Examples using `WHEN…DO`