Specifying Error Recovery Within ttIsql

Issue 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.

Note:

For syntax of the WHENEVER SQLERROR command, see the ttIsql section in the Oracle TimesTen In-Memory Database Reference.

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