WHENEVER SQLERROR

Purpose

Exits SQL*Plus if a SQL command or PL/SQL block generates an error.

Syntax

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

Terms and Clauses

Refer to the following list for a description of each term or clause:

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 in this chapter for details.

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 Notes

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 command file cause SQL*Plus to exit and return the SQL error code if the SQL UPDATE command fails:

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> UPDATE EMP SET SAL = SAL*1.1

The following SQL command error causes SQL*Plus to exit and return the SQL error code:

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> 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 SQL command error causes SQL*Plus to exit and return the value of the variable my_error_var:

SQL> define my_error_var = 99
SQL> WHENEVER SQLERROR EXIT my_error_var
SQL> UPDATE non_existed_table set col1 = col1 + 1;

UPDATE NON_EXISTED_TABLE set col1 = col1 + 1
       *
ERROR at line 1:
ORA-00942: table or view does not exist

Disconnected from Oracle.....

The following examples show that the WHENEVER SQLERROR command does not have any effect on SQL*Plus commands, but does on SQL commands and PL/SQL blocks:

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> column ename headIing "Employee Name"

Unknown COLUMN option "headiing"

SQL> show non_existed_option

Unknown SHOW option "non_existed_option"
SQL> get non_existed_file.sql
Unable to open "non_existed_file.sql"

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

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> begin
  2    select column_does_not_exiSt from dual;
  3  end;
  4  /

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.....