SQL*Plus User's Guide and Reference Release 8.1.6 A75664-01 |
|
Command Reference, 52 of 52
Exits SQL*Plus if a SQL command or PL/SQL block generates an error.
WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable] [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}
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:
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.....
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|