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:
- WHENEVER OSERROR
Use this form of the command to tell SQL*Plus what to do in the event of an operating-system error.
- WHENEVER SQLERROR
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 SUCCESS
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 FAILURE
Exit with a failure status. The value of failure is operating-system-dependent.
- EXIT value
Exit, and return the value specified as the status.
- EXIT :bind_variable
Exit, and return the value of the specified bind variable as the status.
- CONTINUE
Do not exit if an error occurs. This is the default behavior when you first start SQL*Plus.
- COMMIT
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.
- ROLLBACK
May also be used in conjunction with EXIT and CONTINUE, and causes SQL*Plus to roll back the current transaction when an error occurs.
- NONE
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.