oracle回退脚本怎么写,Oracle 11-sqlplus-发生错误时回滚整个脚本-怎么样？

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?

解决方案

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;

/