SQL SQLERROR

What is SQL SQLERROR?

SQLERROR is an Oracle SQL*Plus directive (used with WHENEVER) that defines how the client should react when a SQL statement raises an error.

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 SQLERROR: Supported: Oracle SQL*Plus, Oracle SQLcl Not supported: PostgreSQL, MySQL, SQL Server, SQLite, Standard SQL

SQL SQLERROR Full Explanation

SQLERROR is not a DML or DDL statement but a scripting command understood by Oracle SQL*Plus and Oracle SQLcl. It is used in the form WHENEVER SQLERROR to set an error-handling policy for the remainder of a script. After each SQL or PL/SQL statement, SQL*Plus checks the SQLCODE. If it is non-zero, SQLERROR triggers the action specified (EXIT or CONTINUE) and optionally issues a COMMIT or ROLLBACK.With EXIT, SQL*Plus terminates and returns a status code to the operating system, letting calling shells or CI jobs detect failure. With CONTINUE, the script keeps running. You can supply an explicit numeric return code, a bind variable, or the keywords SUCCESS/FAILURE. COMMIT or ROLLBACK decides what to do with the current transaction before exiting or continuing. Because SQLERROR is evaluated on the client side, it does not affect database transaction semantics beyond the optional commit or rollback.SQLERROR is frequently paired with WHENEVER OSERROR, which handles operating-system errors (for example, failure to spool a file). Together they allow robust, automation-friendly SQL*Plus scripts. Other tools (psql, mysql, sqlcmd) do not support SQLERROR, so attempting to run these commands outside Oracle SQL*Plus will raise a syntax error.

SQL SQLERROR Syntax

WHENEVER SQLERROR EXIT [SUCCESS | FAILURE | <integer> | :bind_variable] [COMMIT | ROLLBACK]

WHENEVER SQLERROR CONTINUE [COMMIT | ROLLBACK | NONE]

SQL SQLERROR Parameters

EXIT - Tells SQL*Plus to terminate when an error occurs.
CONTINUE - Tells SQL*Plus to keep running after an error.
SUCCESS (FAILURE) - Return 0 (success) or 1 (failure) to the OS.
bind_variable - Exit status taken from a numeric bind variable.
COMMIT - Commit pending changes before exiting/continuing.
ROLLBACK - Roll back pending changes before exiting/continuing.
NONE - Do nothing to the transaction before continuing (CONTINUE only).

Example Queries Using SQL SQLERROR

-- Stop the script and roll back when an error occurs
WHENEVER SQLERROR EXIT 1 ROLLBACK;

DELETE FROM orders
WHERE order_date < SYSDATE - 365;

-- If the DELETE fails, SQL*Plus rolls back and exits with status 1

-- Continue on error but keep changes already made
WHENEVER SQLERROR CONTINUE COMMIT;

INSERT INTO audit_log VALUES ('step1', SYSDATE);
EXEC some_procedure();
-- If some_procedure raises ORA- errors, the script commits and moves on

Expected Output Using SQL SQLERROR

In the first block, any ORA- error encountered after the WHENEVER SQLERROR command causes SQL*Plus to roll back the current transaction and exit with operating-system status code 1
In the second block, errors are logged, the transaction is committed, and the script continues executing subsequent statements

Use Cases with SQL SQLERROR

Automating database maintenance in shell or CI pipelines.
Ensuring data integrity by rolling back on failure within long scripts.
Returning meaningful exit codes to orchestration tools (Ansible, Jenkins, cron).
Allowing non-critical steps to continue while still logging errors.

Common Mistakes with SQL SQLERROR

Expecting WHENEVER SQLERROR to work inside PL/SQL blocks (it only applies at the SQL*Plus level).
Using it in tools other than SQL*Plus/SQLcl, resulting in syntax errors.
Forgetting to place the WHENEVER SQLERROR command early, leaving parts of the script unprotected.
Omitting COMMIT/ROLLBACK and unintentionally leaving open transactions.

First Introduced In

Oracle SQL*Plus 1.0

Frequently Asked Questions

What is the difference between WHENEVER SQLERROR EXIT and CONTINUE?

EXIT stops the script and returns a status code to the operating system, while CONTINUE lets the script keep running after logging the error.

Does WHENEVER SQLERROR work inside a PL/SQL procedure?

No. The directive is interpreted by SQL*Plus before sending statements to the database. For in-procedure handling, use PL/SQL EXCEPTION blocks.

How can I return a custom exit code?

Specify a numeric value or a bind variable after EXIT. Example: `WHENEVER SQLERROR EXIT 42` returns 42 to the shell on failure.

What happens if I omit COMMIT or ROLLBACK?

SQL*Plus leaves the transaction open. Exiting without an explicit COMMIT or ROLLBACK usually results in an implicit rollback on the server side.