Skip Headers
SQL*Plus® User's Guide and Reference
Release 10.2

Part Number B14357-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

WHENEVER SQLERROR

Syntax

WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}

Performs the specified action (exits SQL*Plus by default) if a SQL command or PL/SQL block generates an error.

In iSQL*Plus, performs the specified action (stops the current script by default) and returns focus to the Workspace if a SQL command or PL/SQL block generates an error.

Terms

[SUCCESS | FAILURE | WARNING | n | variable | :BindVariable]

Directs SQL*Plus to perform the specified action as soon as it detects a SQL command or PL/SQL block error (but after printing the error message). SQL*Plus will not exit on a SQL*Plus error.

EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable]

Directs SQL*Plus to exit as soon as it detects a SQL command or PL/SQL block error (but after printing the error message). SQL*Plus will not exit on a SQL*Plus error. The EXIT clause of WHENEVER SQLERROR follows the same syntax as the EXIT command. See EXIT for more information.

CONTINUE

Turns off the EXIT option.

COMMIT

Directs SQL*Plus to execute a COMMIT before exiting or continuing and save pending changes to the database.

ROLLBACK

Directs SQL*Plus to execute a ROLLBACK before exiting or continuing and abandon pending changes to the database.

NONE

Directs SQL*Plus to take no action before continuing.

Usage

The WHENEVER SQLERROR command is triggered by SQL command or PL/SQL block errors, and not by SQL*Plus command errors.

Examples

The commands in the following script cause iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace if the SQL UPDATE command fails:

The commands in the following script cause SQL*Plus to exit and return the SQL error code if the SQL UPDATE command fails:

WHENEVER SQLERROR EXIT SQL.SQLCODE
UPDATE EMP_DETAILS_VIEW SET SALARY = SALARY*1.1;

The following SQL command error causes iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace if the SELECT command fails:

WHENEVER SQLERROR EXIT SQL.SQLCODE
select column_does_not_exiSt from dual;
select column_does_not_exist from dual
       *
ERROR at line 1:
ORA-00904: invalid column name

Disconnected from Oracle.....

The following examples show that the WHENEVER SQLERROR command is not executed after errors with SQL*Plus commands, but it is executed if SQL commands or PL/SQL blocks cause errors:

WHENEVER SQLERROR EXIT SQL.SQLCODE
column LAST_name headIing "Employee Name"
Unknown COLUMN option "headiing"

SHOW non_existed_option

The following PL/SQL block error causes SQL*Plus to exit and return the SQL error code:

WHENEVER SQLERROR EXIT SQL.SQLCODE
begin
  SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
END;
/
SELECT COLUMN_DOES_NOT_EXIST FROM DUAL;
       *
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00201: identifier 'COLUMN_DOES_NOT_EXIST' must be declared
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored

Disconnected from Oracle.....