Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Name

WHENEVER

Synopsis

The WHENEVER command controls the behavior of SQL*Plus when an operating system or SQL error occurs and is discussed in Chapter 11. You can choose between having SQL*Plus exit immediately or continue on whenever an error occurs. You can choose whether to automatically COMMIT or ROLLBACK in the event of an error. 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.

Tip

When used from iSQL*Plus, WHENEVER performs the action you specify (e.g., COMMIT or ROLLBACK) and returns you to the iSQL*Plus input area. You aren't disconnected from your database session.

Syntax

WHENEVER {OSERROR | SQLERROR}
         {EXIT [SUCCESS | FAILURE | value | :bind_variable | sub_variable]
               [COMMIT | ROLLBACK]
         | CONTINUE [COMMIT | ROLLBACK | NONE]}

Parameters

WHENEVER OSERROR

Use this command form to tell SQL*Plus what to do in case an operating system error occurs.

WHENEVER SQLERROR

Use this command form to tell SQL*Plus what to do in case an error is returned from a SQL statement or PL/SQL block.

EXIT SUCCESS

Exit with a success status. The exact value of the success status is operating-system-dependent but is often 0. 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 the failure status is operating-system-dependent ...