SQL WHENEVER

What is the SQL WHENEVER command?

Defines an automatic action (EXIT or CONTINUE) that a client, script, or embedded SQL block should take when a specified error condition occurs.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL WHENEVER: Oracle SQL*Plus/SQLcl, Oracle SQL Developer worksheet, IBM DB2 CLP, Informix DB-Access, PostgreSQL ECPG, MySQL embedded C, MariaDB embedded C, Teradata BTEQ

SQL WHENEVER Full Explanation

WHENEVER is an error-handling directive, not a data-manipulation statement. In Oracle SQL*Plus (and compatible tools such as SQLcl) it instructs the client to automatically EXIT or CONTINUE when an SQLERROR (database error) or OSERROR (operating-system error) occurs while a script is running. EXIT can optionally commit or roll back the current transaction and return a status code to the calling shell.In embedded SQL for host languages (Pro*C, IBM DB2, Informix ESQL/C, MySQL C API, PostgreSQL ECPG) WHENEVER is compiled into the application. It traps runtime conditions (SQLERROR, SQLWARNING, NOT FOUND) and triggers an action such as GOTO label, DO procedure, or STOP. This lets developers centralize error handling without wrapping every statement in explicit checks.Key points:- WHENEVER sets a stateful directive that remains in force until redefined.- Multiple conditions can be set separately (e.g., one rule for SQLERROR and another for NOT FOUND).- In SQL*Plus, EXIT ends the client session, returning the specified value to the operating system. CONTINUE resumes script execution.- Transaction outcome is governed by optional COMMIT or ROLLBACK keywords.- The directive is evaluated after each SQL or host command; it does not change database semantics, only client behavior.- WHENEVER is client-side; it does not exist inside PL/SQL or server-side stored procedures.

SQL WHENEVER Syntax

-- SQL*Plus / SQLcl
WHENEVER {SQLERROR | OSERROR} {EXIT | CONTINUE} [SUCCESS | FAILURE | WARNING | <integer>] [COMMIT | ROLLBACK]

-- Embedded SQL (generic)
EXEC SQL WHENEVER {SQLERROR | SQLWARNING | NOT FOUND} action;

SQL WHENEVER Parameters

condition (enum) - SQLERROR, OSERROR (SQL*Plus) or SQLERROR, SQLWARNING, NOT FOUND (embedded SQL)
action (enum) - EXIT or CONTINUE (SQL*Plus) or GOTO label, CALL procedure, STOP, CONTINUE (embedded)
status_code (int) - Numeric code returned to host shell when EXIT is used (SQL*Plus only)
commit_flag (enum) - COMMIT or ROLLBACK transaction when EXIT/CONTINUE fires (SQL*Plus only)

Example Queries Using SQL WHENEVER

-- Stop a deployment script if any SQL fails, roll back, and return code 1
WHENEVER SQLERROR EXIT 1 ROLLBACK;

-- Continue on OS file-system errors but keep running
WHENEVER OSERROR CONTINUE;

-- Embedded Pro*C example: jump to err_lbl on NOT FOUND
EXEC SQL WHENEVER NOT FOUND GOTO err_lbl;
...
err_lbl:
    /* error handling here */

Expected Output Using SQL WHENEVER

WHENEVER sets an internal directive
No rows are returned
On the first qualifying error, SQL*Plus exits (or continues) with the specified commit/rollback and status code; embedded SQL branches as configured

Use Cases with SQL WHENEVER

Automated migration scripts that must abort on the first failed statement
CI/CD pipelines where a non-zero exit code indicates failure
Data-load batch jobs that need to roll back if any step fails
Embedded C or COBOL programs that route NOT FOUND conditions to common error handlers

Common Mistakes with SQL WHENEVER

Assuming WHENEVER works inside PL/SQL blocks (it does not)
Forgetting to reset the directive, causing later scripts to exit unexpectedly
Omitting COMMIT/ROLLBACK and mistakenly leaving an open transaction
Using EXIT without specifying a return code, which may default to success in some shells

First Introduced In

Oracle SQL*Plus 1.0 (1980); ANSI Embedded SQL-86 for host languages

Frequently Asked Questions

What does SQL WHENEVER do?

It defines an automatic EXIT or CONTINUE action when a specified error condition occurs during script or embedded SQL execution.

How can I make a SQL*Plus script fail fast?

Place `WHENEVER SQLERROR EXIT 1 ROLLBACK;` at the top. The script stops on the first SQL error, rolls back, and returns exit code 1 to the shell.

Can I use WHENEVER inside PL/SQL packages?

No. WHENEVER is interpreted by the client or embedded SQL pre-compiler, not by the PL/SQL engine. Use EXCEPTION blocks inside PL/SQL instead.

Is WHENEVER portable across databases?

The concept exists in most embedded SQL implementations, but the syntax outside Oracle SQL*Plus may vary. Always check your client documentation before porting.