WHENEVER SQLERROR CLPPlus command

The WHENEVER SQLERROR CLPPlus command specifies the action CLPPlus performs when an SQL error occurs in SQL or PL/SQL. This command allows you to trap errors and control CLPPlus behavior by performing specified actions like EXIT or CONTINUE.

Invocation

This command must be executed from the CLPPlus interface.

Authorization

None

Required connection

None

Command syntax

Read syntax diagramSkip visual syntax diagramWHENEVER SQLERROR CONTINUENONECOMMITROLLBACKEXITSUCCESSFAILUREWARNINGvaluevariable:bindvariableCOMMITROLLBACK

Command parameters

CONTINUE
Directs CLPPlus to continue with a specified action when an SQL or PL/SQL error is encountered.
NONE
The default value used in the WHENEVER SQLERROR CONTINUE command. No action on the block of SQL generating an error is taken.
COMMIT
When COMMIT is specified in the WHENEVER SQLERROR CONTINUE command, any possible work done by the current SQL block is committed.
ROLLBACK
When ROLLBACK is specified in the WHENEVER SQLERROR CONTINUE command, all work in the current SQL block is rolled back.
EXIT
Directs CLPPlus to exit once an SQL or PL/SQL error is encountered. The functionality of this option is the same as the stand-alone EXIT command.
SUCCESS
Returns an operating system-dependant return code indicating success. The is the first default EXIT parameter.
FAILURE
Returns an operating system-dependant return code indicating failure.
WARNING
Returns an operating system-dependant return code indicating a warning.
value
Specifies a variable created by the DEFINE command whose value is returned as the return code.
variable
Specifies a substitution variable value created by the DEFINE command whose value is returned as the return code.
:bindvariable
Specifies a Bind variable value created by the DEFINE command whose value is returned as the return code.
COMMIT
Specifies that uncommitted updates are committed when the CLPPlus session ends. The is the second default EXIT parameter.
ROLLBACK
Specifies that uncommitted updates are rolled back when the CLPPlus session ends.

Examples

The following example shows the WHENEVER SQLERROR CONTINUE command behavior. The CLPPlus prompt is returned and CLPPlus is still available for use.

SQL> whenever sqlerror continue
SQL> select * from nonexistingtable;
SQL0204N "SCHEMA.NONEXISTINGTABLE" is an undefined name.
SQL>

You can also commit, rollback, or take no action whenever an SQL error occurs.

SQL> whenever sqlerror continue commit 
SQL>
SQL> whenever sqlerror continue rollback
SQL>
SQL> whenever sqlerror continue none
SQL>

The following examples use the EXIT option to exit the CLPPlus application.

SQL> whenever sqlerror exit
SQL> select * from nonexistingtable;
SQL0204N "SCHEMA.NONEXISTINGTABLE" is an undefined name.

C:\>

The following specify the error code returned during exit. This behavior is identical to the EXIT CLPPlus command.

SQL> whenever sqlerror exit success
SQL> whenever sqlerror exit failure
SQL> select * from nonexistingtable;
SQL0204N "SCHEMA.NONEXISTINGTABLE" is an undefined name.

C:\echo %errorlevel%
1


SQL> define exit_value=6
SQL> whenever sqlerror exit exit_value
SQL> select * from nonexistingtable;
SQL0204N "SCHEMA.NONEXISTINGTABLE" is an undefined name.

C:\echo %errorlevel%
6

Similar to the EXIT CLPPlus command, you can specify whether to commit or rollback while exiting the CLPPlus application.

SQL> whenever sqlerror exit 2 commit

SQL> whenever sqlerror exit 2 rollback