SQL*Plus User's Guide and Reference Release 9.0.1 Part Number A88827-02 |
|
Command Reference, 52 of 52
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.
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
COMMIT
ROLLBACK
NONE
The WHENEVER SQLERROR command is triggered by SQL command or PL/SQL block errors, and not by SQL*Plus command errors.
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.....
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|