如何在包含的sql文件中的任何错误上使Oracle 11g回滚整个事务？
set autocommit off whenever sqlERROR EXIT ROLLBACK insert into a values (1); insert into a values (2); drop index PK_NOT_EXIST; commit;
@error.sql
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条记录,这意味着在退出sqlplus时有一个提交而不是回滚.
我可以以某种方式强制sqlplus：
>出错时停止处理文件,
>并在出错时回滚整个交易？
解决方法
DDL在运行之前和之后执行提交,这样即使您的DDL失败,oracle也已经提交了事务.
你可以解决它：
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; /
