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

一、ORA-06519错误出现
我往数据库中一个表中写数据，这个表中对insert做了一个自治事务触发器
后台报错， ORA-06519: 检测到活动的自治事务处理，已经回退
请问一下，这个自治触发器应该如何处理才能避免这种问题
二、错误解释
[oracle@CCN-BJ-1-538 ~]$ 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;
   gt_operateswitch_pkg.operate_switch_end@pw2gis (: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后成功执行。