Handling Error Conditions

When we run SQL statements interactively in SQL*Plus or SQL Developer or another utility, we decide
how to react to an error. If a SQL statement fails from a syntax error or from an unexpected data
condition such as ORA-01403: no rows found, do we want to proceed and run the next SQL statement, or do we want to simply roll back all work that has been done and exit? When executing interactively, we
can decide interactively. But what about when we’re running a script?
SQL*Plus provides the WHENEVER command to direct SQL*Plus how to react to failures. WHENEVER is
particularly useful when running a script. Table 11-7 describes two variations of the command.

Table 11-7. WHENEVER Error-Handling Conditions

---

Error condition  Description

---

WHENEVER OSERROR  Triggers whenever a SQL*Plus command like CONNECT, DISCONNECT, SPOOL, HOST,
START, or any other command which interacts with the operating system fails.
WHENEVER SQLERROR  Triggers whenever a SQL statement like SELECT, INSERT, UPDATE, DELETE, CREATE,
ALTER, DROP, TRUNCATE, GRANT, REVOKE, or any other SQL command fails.

---

 

Table 11-8. WHENEVER Error-Handing Directives

---

Error condition  Description

---

EXIT [ exit-status | txn-directive ]  Exit from SQL*Plus with the specified exit status after
committing or rolling back the current transaction as directed.
CONTINUE [ txn-directive ]   Continue executing SQL*Plus after committing, rolling back,
or doing nothing.

Exit-status  Can be one of:
 
[ SUCCESS | FAILURE | n | substitution-variable | bind-
variable ] 
 
where SUCCESS is an operating-system dependent exit status signifying successful completion, FAILURE is an operating-
system dependent exit status signifying failure, and n is a
number value. SQL*Plus substitution variables and SQL*Plus
bind variables containing numeric values can also be used as
return statuses. SUCCESS is the default.
Txn-directive  Can be one of:
 
[ COMMIT | ROLLBACK | NONE ]  
 
where NONE can be used only with the CONTINUE directive.
When used with the EXIT directive, COMMIT is the default and
when used with the CONTINUE directive, NONE is the default.

---

So, if a SQL*Plus script. contains five UPDATE statements in a row and you want the script. to stop
executing, roll back any work already performed, and then exit to the operating system with a failure
status, your script. might look something like that shown in Listing 11-45.

Listing 11-45. Error-Handling in a SQL*Plus Script

whenever oserror exit failure rollback
whenever sqlerror exit failure rollback
set echo on feedback on timing on
spool update_script.
update …
update …
update …
update …
update …
exit success commit (*由此可以看出不在前面加whenever條件也是可以的，因為exit、continue都是指令，都可以單獨使用的)
 
In Listing 11-45, we see the use of the WHENEVER command directing SQL*Plus to exit back to the
operating system with a FAILURE exit status, and perform. a ROLLBACK as it does so, should any OS
commands (such as SPOOL) or SQL commands (such as UPDATE) fail. If all of the commands are successful
and we reach the very last line of the script, then we will EXIT back to the operating system with SUCCESS
exit status and perform. a COMMIT as it does so.