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]}
alexlima.com 