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:

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

DazzaL · Accepted Answer · 2013-02-08 09:54:38Z

5

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;
/

answered Feb 8, 2013 at 9:54

DazzaL

22.1k3 gold badges52 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Grzegorz Over a year ago

Thanks. The problem with it is that DDLs that succeed will anyway be committed. So I will get it inconsistent any way. It seems that the only way to apply such a script is to do it step by step, stop on error, and after fixing it continue from where it failed.

Grzegorz · Accepted Answer · 2013-02-08 09:53:34Z

4

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.

answered Feb 8, 2013 at 9:53

Grzegorz

5041 gold badge3 silver badges14 bronze badges

1 Comment

Andrew Spencer Over a year ago

To confirm, I just tested a case on 12c with some DDL followed by DML. If you execute ddl_statement; dml_statement_1; dml_statement_2; and there is an error in dmlstatement_2, then it will commit (of course) ddl_statement but rollback dml_statement_1 and dml_statement_2. (This turned out to be acceptable for our case.)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related