Syntax for the WHENEVER SQLERROR Command

Run the WHENEVER SQLERROR command to prescribe what to do when a SQL error occurs. For more details and examples on how to use the WHENEVER SQLERROR command, see Specifying Error Recovery Within ttIsql command in the Oracle TimesTen In-Memory Database Operations Guide.

WHENEVER SQLERROR { ExitClause | ContinueClause | SUPPRESS |
     SLEEP Number | ExecuteClause }

When you specify EXIT, always exit ttIsql if an error occurs. ExitClause is as follows:

EXIT [ SUCCESS | FAILURE | WARNING | Number | :BindVariable ]
 [ COMMIT | COMMIT ALL | ROLLBACK ]

When you specify CONTINUE, ttIsql continues to the next command, even if an error occurs. ContinueClause is as follows:

CONTINUE [ COMMIT | COMMIT ALL | ROLLBACK | NONE ]

Run specified commands before continuing. ExecuteClause is as follows:

EXECUTE "Cmd1;Cmd2;...;"

The WHENEVER SQLERROR command options are as follows:

• EXIT: Always exit ttIsql if an error occurs. Specify what is performed before ttIsql exits with one of the following. SUCCESS is the default option for EXIT.

  • SUCCESS or FAILURE or WARNING: Return SUCCESS (value 0), FAILURE (value 1), or WARNING (value 2) to the operating system after ttIsql exits for any SQL error.

  • Number: Specify a number from 0 to 255 that is returned to the operating system as a return code. Once ttIsql exits, you can retrieve the error return code with the appropriate operating system commands. For example, use echo $status in the C shell (csh) or echo $? in the Bourne shell (sh) to display the return code.

    The return code can be retrieved and processed within batch command files to programmatically detect and respond to unexpected events.

  • :BindVariable: Returns the value in a bind variable that was previously created in ttIsql with the variable command. The value of the variable at the time of the error is returned to the operating system in the same manner as the Number option.

    Note:

    The bind variable used within the WHENEVER SQLERROR command cannot be defined as a LOB, REFCURSOR, or any array data type.

  In addition, you can specify whether to commit or rollback all changes before exiting ttIsql.

  • COMMIT: Runs a COMMIT and saves changes only in the current connection before exiting. The other connections exit with the normal disconnect processing, which rolls back any uncommitted changes.

  • COMMIT ALL: Runs a COMMIT and saves changes in all connections before exiting.

  • ROLLBACK: Before exiting, runs a ROLLBACK and abandons changes in the current connection and, by default, in all other connections. The other connections exit with the normal disconnect processing, which automatically rolls back any uncommitted changes.

• CONTINUE: Do not exit if an error occurs. The SQL error is displayed, but the error does not cause ttIsql to exit. The following options enable you to specify what is done before continuing to the next ttIsql command:

  • NONE: This is the default. Take no action before continuing.

  • COMMIT: Runs a COMMIT and saves changes in the current connection before continuing.

  • COMMIT ALL: Runs a COMMIT and saves changes in all connections before continuing.

  • ROLLBACK: Before continuing, runs a ROLLBACK and abandons changes in the current connection and, by default, in all other connections. The other connections exit with the normal disconnect processing, which automatically rolls back any uncommitted changes.

• SUPPRESS: Do not show any error messages and continue.

• SLEEP: Sleep for a specified number of seconds before continuing.

• EXECUTE: Run specified commands before continuing. Each command is separated from the other commands by a semicolon (;). If any command triggers additional errors, those errors may cause additional actions that could potentially result in a looping condition.