Oracle 11-sqlplus-发生错误时回滚整个脚本-怎么样? [英] Oracle 11 - sqlplus - rollback the whole script on error - how?

问题描述

如何在包含的SQL文件中出现任何错误时使Oracle 11g回滚整个事务?

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

文件内容为:

set autocommit off
whenever SQLERROR EXIT ROLLBACK

insert into a values (1);
insert into a values (2);

drop index PK_NOT_EXIST;

commit;

该文件使用"@"包含在sqlplus会话中:

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

@error.sql

如预期的那样，sqlplus会话终止并且输出为

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

但是当我重新启动sqlplus时，表a包含2条记录，这意味着存在 commit ，而不是在sqlplus退出时回滚.

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.

我可以以某种方式强制sqlplus执行以下操作吗?

Can I somehow force sqlplus to:

1. 错误停止处理文件
2. 并在出错时回滚整个交易吗?

推荐答案

DDL在它运行之前和之后执行一次提交，这样，即使您的DDL失败，oracle也已经提交了该事务.

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

这篇关于Oracle 11-sqlplus-发生错误时回滚整个脚本-怎么样?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！