WHENEVER SQLERROR

Syntax

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

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

Terms

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

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:

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

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

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

define my_error_var = 99
WHENEVER SQLERROR EXIT my_error_var
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:

WHENEVER SQLERROR EXIT SQL.SQLCODE
column LAST_name headIing "Employee Name"


Unknown COLUMN option "headiing"


SHOW non_existed_option


Unknown SHOW option "non_exist_option"

GET non_existed_file.sql


Unable to open "non_exist_file.sql"

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