oracle 自治事务异常不回滚,ORA-06519: 检测到活动的自治事务处理，已经回退

有位朋友在使用自治事务触发器调用远程的存储过程出现错误ORA-06519

一、ORA-06519错误出现

我往数据库中一个表中写数据，这个表中对insert做了一个自治事务触发器

后台报错， ORA-06519: 检测到活动的自治事务处理，已经回退

请问一下，这个自治触发器应该如何处理才能避免这种问题

二、错误解释

[[email protected] ~]$ oerr ora 6519

06519, 00000, "active autonomous transaction detected and rolled back"

// *Cause:   Before returning from an autonomous PL/SQL block, all autonomous

//           transactions started within the block must be completed (either

//           committed or rolled back). If not, the active autonomous

//           transaction is implicitly rolled back and this error is raised.

// *Action:  Ensure that before returning from an autonomous PL/SQL block,

//           any active autonomous transactions are explicitly committed

//           or rolled back.

//-----------------------------------------------------------------------

//

// 06520 through 06529 reserved for Foreign function errors

//

三触发器内容：

CREATE OR REPLACE TRIGGER get_pi_ip_cus_insert

AFTER INSERT

ON pw_attemper_correlative_area

REFERENCING NEW AS NEW

FOR EACH ROW

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

COMMIT;

(:NEW.OID,

:NEW.log_id,

:NEW.from_area_id,

:NEW.to_area_id,

:NEW.pi_type,

:NEW.start_time,

:NEW.end_time

);

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END;

四、metalink给出的解决方案：

TIP:  Click help for a detailed explanation of this page.

书签 转到末尾

主题:  ORA-06519 ORA-06512 Errors When Executing A Procedure Having Autonomous Transaction

文档 ID:  注释:309285.1 类型:  PROBLEM

上次修订日期:  11-OCT-2007 状态:  PUBLISHED

"Checked for relevance on 11-OCT-2007"

In this Document

Symptoms

Cause

Solution

References

Applies to:

PL/SQL - Version: 9.2.0.6

This problem can occur on any platform.

Symptoms

The following errors occur when executing a PL/SQL procedure:

ORA-06519: active autonomous transaction detected and rolled back

ORA-06512: at "%s.%s", line 36

ORA-06512: at line 4

This PL/SQL Procedure uses Autonomous Transactions.

Cause

If no commit or rollback is done before exiting the PL/SQL Procedure, then at the point of executing the "return" or "end" statement the whole autonomous transaction is rolled back with the errors mentioned above.

Solution

Add a commit or rollback statement in the code so that the PL/SQL Procedure gets successfully compiled and executed.

Note: If there are any Exception Handler Section in the code, then add a commit or rollback statement for every exception that is handled.

References

Note 75199.1 - OERR: ORA-6519 active autonomous transaction detected and rolled back

Errors

ORA-6512 "at %sline %s"

ORA-6519 active autonomous transaction detected and rolled back

Keywords

'AUTONOMOUS'

经过检查果然是远程存储过程没有commit或rollback，加上commit后成功执行。