The WHENEVER command controls the behavior of SQL*Plus when an operating-system or SQL error occurs, and is discussed in Chapter 7. You can choose between having SQL*Plus exit immediately or continue on whenever an error occurs. You can also choose whether to automatically COMMIT or ROLLBACK in the event of an error. Finally, if you decide to abort in the event of an error, you can pass a value back to the operating system. If you are calling SQL*Plus from an operating-system script, you can use this return value to determine that script’s next course of action.
WHENEVER {OSERROR|SQLERROR} {EXIT [SUCCESS|FAILURE|value
|:bind_variable
|] [COMMIT|ROLLBACK] |CONTINUE [COMMIT|ROLLBACK|NONE]}
where:
Use this form of the command to tell SQL*Plus what to do in the event of an operating-system error.
Use this form of the command to tell SQL*Plus what to do in the event that an error is returned from a SQL statement or PL/SQL block.
Exit with a success status. The exact value of success is operating-system-dependent. This is the default setting, and it applies if the EXIT keyword is used without specifying any return value.
Exit with a failure status. The value of failure is operating-system-dependent.
Exit, and return the value specified as the status.
Exit, and return the value of the specified bind variable as the status.
Do not exit if an error occurs. This is the default behavior when you first start SQL*Plus.
This keyword may be used in conjunction with both EXIT and CONTINUE. It causes SQL*Plus to automatically COMMIT the current transaction when an error occurs. This is the default behavior when you use the EXIT keyword.
May also be used in conjunction with EXIT and CONTINUE, and causes SQL*Plus to roll back the current transaction when an error occurs.
May only be used in conjunction with CONTINUE, and causes SQL*Plus to neither COMMIT nor ROLLBACK when an error occurs. This is the default behavior when you use the CONTINUE keyword.