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