Geek Answers Handbook

Oracle 11 - sqlplus - rollback entire script on error - how?

How can I get Oracle 11g to cancel the entire transaction if there is any error in the included SQL file?

File contents:

set autocommit off whenever SQLERROR EXIT ROLLBACK insert into a values (1); insert into a values (2); drop index PK_NOT_EXIST; commit;

And the file is included in the sqlplus session using "@":

 @error.sql

As expected, the sqlplus session ends and the exit

 SQL> @error.sql 1 row created. 1 row created. drop index PK_NOT_EXIST * ERROR at line 1: ORA-01418: specified index does not exist Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

But when I restart sqlplus, then table a contains 2 records, which means that when sqlplus exited it was commit , not rollback.

Is there any way to force sqlplus:

  • stop processing the file on error,
  • and rollback of the entire transaction on error?
+4
source share
2 answers

DDL commits before it starts and after that, even if your DDL does not work, oracle has already committed the transaction.

you can get around it with:

 set autocommit off whenever SQLERROR EXIT ROLLBACK declare procedure drop_idx(i varchar2) is pragma autonomous_transaction; -- this runs in its own transaction. begin execute immediate 'drop index ' || i; end; begin insert into a values (1); insert into a values (2); drop_idx('PK_NOT_EXIST'); end; /
+2
source

I solved the problem and I am posting a solution if someone gets into such a problem.

If I did not put the DDL commands in the script, then the rollback is performed correctly.

So the script:

 set autocommit off whenever SQLERROR EXIT ROLLBACK insert into a values (1); insert into a values (2); insert into a values ('x'); commit;

works.

And if DDL is used, then in general, Oracle does not provide a rollback function.

+2
source

More articles:


All Articles