SQLPlus Whenever Sqlerror

The WHENEVER SQLERROR command is used in SQLPlus to specify how the tool should handle errors encountered during the execution of SQL statements. It allows you to define specific actions that should be taken when an error occurs, giving you control over the behavior of SQLPlus in such situations.

Syntax

The syntax for the WHENEVER SQLERROR command is as follows:

WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n]|CONTINUE [COMMIT|ROLLBACK|NONE]}

The command consists of two parts: the error condition and the action to be taken. The error condition can be one of the following:

EXIT SUCCESS: Causes SQL*Plus to exit with a successful completion code.
EXIT FAILURE: Causes SQL*Plus to exit with a failure completion code.
EXIT WARNING: Causes SQL*Plus to exit with a warning completion code.
EXIT n: Causes SQL*Plus to exit with the specified n as the completion code.
CONTINUE COMMIT: Instructs SQL*Plus to continue executing the next statement after an error, but issues a commit before continuing.
CONTINUE ROLLBACK: Instructs SQL*Plus to continue executing the next statement after an error, but issues a rollback before continuing.
CONTINUE NONE: Instructs SQL*Plus to continue executing the next statement after an error without issuing any commit or rollback.

By using the WHENEVER SQLERROR command, you can control the flow of your SQLPlus script or session based on the occurrence of errors. For example, if you want to exit SQLPlus immediately when an error occurs, you can use the following command:

WHENEVER SQLERROR EXIT FAILURE

This ensures that any error encountered during the execution of SQL statements will cause SQL*Plus to exit with a failure completion code.

On the other hand, if you want to continue executing subsequent statements after an error occurs, you can use the following command:

WHENEVER SQLERROR CONTINUE NONE

This allows SQL*Plus to continue executing the script or session without issuing any commit or rollback. This can be useful when you want to ignore specific errors and continue processing the remaining statements.

Overall, the WHENEVER SQLERROR command in SQL*Plus provides flexibility in handling errors during SQL statement execution. It allows you to define the desired behavior when errors occur, enabling you to control the flow of your script or session accordingly.