Oracle 11 - sqlplus - rollback the whole script on error - how?

Question

How can I make Oracle 11g rollback the whole transaction on any error in included SQL file?

The file contents are:

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 into sqlplus session using "@":

@error.sql

As expected, the sqlplus session terminates and the output is

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 re-launch sqlplus then the table a contains 2 records which means that there was a commit and not a rollback on exit of sqlplus.

Can I somehow force sqlplus to:

1. stop processing file on error,
2. and rollback the whole transaction on error?

Answer 1

I solved the issue and I post back the solution in case anyone hits such a problem.

If I don't put 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 rollback functionality.

Answer 2

DDL performs a commit before it runs and after so that even if your DDL fails, oracle will have already committed the transaction.

you could work 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;
/