一,、ORA-06519錯誤出現(xiàn)
我往數(shù)據(jù)庫中一個表中寫數(shù)據(jù)，這個表中對insert做了一個自治事務(wù)觸發(fā)器
后臺報錯,， ORA-06519: 檢測到活動的自治事務(wù)處理,，已經(jīng)回退
請問一下，這個自治觸發(fā)器應(yīng)該如何處理才能避免這種問題
二,、錯誤解釋
[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
//
三觸發(fā)器內(nèi)容：

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.
 書簽 轉(zhuǎn)到末尾

主題:  ORA-06519 ORA-06512 Errors When Executing A Procedure Having Autonomous Transaction
  文檔 ID:  注釋:309285.1 類型:  PROBLEM
  上次修訂日期:  11-OCT-2007 狀態(tài):  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'  

 

經(jīng)過檢查果然是遠程存儲過程沒有commit或rollback，加上commit后成功執(zhí)行。