SQL Reference - WHENEVER

WHENEVER

WHENEVER is a directive in SQL that specifies an action to be taken when a certain SQLCODE or SQLSTATE condition is detected by an SQL statement. The possible actions include CONTINUE, which controls the impact on the overall SQL statement with the error, and STOP, which has the effect of a RETURN statement and the SQL statement in which the error occurred is rolled back.

Example

DECLARE
  DivideByZero EXCEPTION;
BEGIN
  WHENEVER SQLERROR RAISE DivideByZero;
  EXECUTE IMMEDIATE 'SELECT 100/0 FROM dual';
EXCEPTION
  WHEN DivideByZero THEN
    DBMS_OUTPUT.PUT_LINE('Error: Attempted division by zero.');
END;

Output

Error: Attempted division by zero.

Explanation

In the provided SQL block for Oracle, an exception DivideByZero is declared. The WHENEVER SQLERROR command sets the action Oracle Database should take if it encounters a SQL command with an error.

In this instance, it executes the SQL command SELECT 100/0 FROM dual (which would result in a divide by zero error). The exception DivideByZero is raised and the string ‘Error: Attempted division by zero.’ is printed.