How to terminate and rollback a SQL script when running in SQL*Plus when an error is encountered?

Adding the following line before running a SQL Script will terminate the SQL script from running and rollback the change when an error is encountered.

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

Example:
SQL> select * FROM test;

no rows selected

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
SQL> insert into test values ( 1 );

1 row created.

— error in the script it terminates the session
SQL> insert into test ( ‘aaaa’);
insert into test ( ‘aaaa’)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword

— upon error the session terminates upon encountering an error
Disconnected from Oracle Database ……

— check if the insert was committed
$ sqlplus user/password

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Sep 2 11:44:08 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
— shows the insert was rolled-back
SQL> select * FROM test;

no rows selected

The other options available for WHENEVER SQLERROR are:
WHENEVER SQLERROR
{EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s