whenever sqlerror

Syntax

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.

Description

Provide direction on how to handle errors when in ttIsql. Run the WHENEVER SQLERROR command to prescribe what to do when a SQL error occurs. WHENEVER SQLERROR can be used to set up a recovery action for SQL statements, SQL script, or PL/SQL block.

By default, if a SQL error occurs while in ttIsql, the error information is displayed and ttIsql continues so that you can enter a new command. The default setting is WHENEVER SQLERROR CONTINUE NONE. You can also specify that ttIsql exits each time an error occurs, which may not be the best action for interactive use or when running a SQL script or a PL/SQL block.

Examples

The following example uses EXIT to return an error code of 255 and runs a COMMIT statement to save all changes to the current connection before exiting ttIsql. The example retrieves the error code using the C shell echo $status command.

Command> WHENEVER SQLERROR EXIT 255 COMMIT;
Command> SELECT emp_id FROM employee;
 2206: Table PAT.EMPLOYEE not found
WHENEVER SQLERROR exiting.
% echo $status
255

The following example demonstrates how the WHENEVER SQLERROR command can run ttIsql commands or TimesTen utilities when an error occurs, even if the error is from another TimesTen utility:

Command> WHENEVER SQLERROR EXEC "DSSIZE;CALL TTSQLCMDCACHEINFOGET();";
Command> call TTCACHEPOLICYGET;
 5010: No OracleNetServiceName specified in DSN
The command failed.
 
DSSIZE;
 
  PERM_ALLOCATED_SIZE:      32768
  PERM_IN_USE_SIZE:         9204
  PERM_IN_USE_HIGH_WATER:   9204
  TEMP_ALLOCATED_SIZE:      40960
  TEMP_IN_USE_SIZE:         7785
  TEMP_IN_USE_HIGH_WATER:   7848
 
CALL TTSQLCMDCACHEINFOGET();
 
CMDCOUNT, FREEABLECOUNT, SIZE
< 10, 7, 41800 >
1 row found.

The following demonstrates the SUPPRESS command option. It suppresses all error messages and continues to the next command. The example shows that the error messages can be turned back on in the existing connection with another command option, which in this case is the EXIT command.

Command> WHENEVER SQLERROR SUPPRESS;
Command> SELECT *;
Command> WHENEVER SQLERROR EXIT;
Command> SELECT *;
 1001: Syntax error in SQL statement before or at: "", character position: 9
select *
        ^
WHENEVER SQLERROR exiting.

The following example sets a bind variable called retcode, the value of which is returned when a SQL error occurs:

Command> VARIABLE retcode NUMBER := 111;
Command> WHENEVER SQLERROR EXIT :retcode;
Command> INSERT INTO EMPLOYEES VALUES (
202, 'Pat', 'Fay', 'PFAY', '603.123.6666',
TO_DATE ('17-AUG-1997', 'DD-MON-YYYY'),
'MK_REP', 6000, NULL, 201, 20);
  907: Unique constraint (EMPLOYEES on PAT.EMPLOYEES) violated at Rowid
 <BMUFVUAAACOAAAAIiB>
WHENEVER SQLERROR exiting.
% echo $status;
111