[Oracle]fnd_global.apps_initialize ORA-02074

Oracle APEX 程式透過DB Link呼叫EBS Package, Package裡有使用fnd_profile.VALUE ('user_id') .但因為不是由EBS呼叫的程式, EBS的USER_ID會是空的, 為了不異動原本程式, 故在呼叫此Package前會先執行fnd_global.apps_initialize.

作法如下:

1. 在EBS產生一個Procedure:

PROCEDURE SET_APPS_INITIAL (p_user_id IN NUMBER, p_resp_id IN NUMBER, p_resp_appl_id IN NUMBER) 
IS
BEGIN
fnd_global.apps_initialize(NVL(p_user_id,0),
                             NVL(p_resp_id,0),
                             NVL(p_resp_appl_id,0)
                            );
END; 

2. APEX程式透過DB Link呼叫此Procedure, 並傳入EBS User ID:

APPS.SET_APPS_INITIAL@DB_LINK_NAME (

    :EBS_USER_ID, 

    :EBS_RESP_ID, 

    :EBS_RESP_APPL_ID);

3. APEX程式再呼叫原本要用的EBS Package:

APPS.OM_CUST.UPD_DELIVERY@DB_LINK_NAME(:DELIVERY_NAME);   

----------------------------------------------------------------------------------------------------------

至此第一次執行沒問題, 但要開始處理第二筆資料時出現錯誤:

ORA-20001: Oracle error -20001: ORA-20001: Oracle error -2074: ORA-02074: cannot SET NLS in a distributed transaction has been detected in fnd_global.set_nls.set_parameter('NLS_LANGUAGE','AMERICAN'). has been detected in fnd_global.set_nls. ORA-06512: at "APPS.APP_EXCEPTION", line 72 ORA-06512: at "APPS.FND_GLOBAL", line 271 ORA-06512: at "APPS.FND_GLOBAL", line 1575 ORA-06512: at "APPS.FND_GLOBAL", line 2006 ORA-06512: at "APPS.FND_GLOBAL", line 2607 ORA-06512: at "APPS.FND_GLOBAL", line 2759 ORA-06512: ORA-06512: at "APEX_210200.WWV_FLOW_CODE_EXEC_PLSQL", line 124 ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 2417 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SYS_SQL", line 2120 ORA-06512: at "SYS.WWV_DBMS_SQL_APEX_210200", line 682 ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 2378 ORA-06512: at "APEX_210200.WWV_FLOW_CODE_EXEC_PLSQL", line 96 ORA-06512: at "APEX_210200.WWV_FLOW_CODE_EXEC_PLSQL", line 301 ORA-06512: at "APEX_210200.WWV_FLOW_CODE_EXEC", line 340

找了很久終於在這查到問題原因及解法:

this issue happens when executing FND_GLOBAL.apps_initialize more than once within a trigger/via a db link in the same transaction.

 http://oracle-apps-dba.blogspot.com/2008/05/fndglobal-affected-by-new-global.html

於是參考第二種作法(APEX沒有用到分散式處理 應該)修改了SET_APPS_INITIAL Procedure:  

PROCEDURE SET_APPS_INITIAL (p_user_id IN NUMBER, p_resp_id IN NUMBER, p_resp_appl_id IN NUMBER) 
IS

                pragma autonomous_transaction;
BEGIN
fnd_global.apps_initialize(NVL(p_user_id,0),
                             NVL(p_resp_id,0),
                             NVL(p_resp_appl_id,0)
                            );

               COMMIT;
END; 

執行了幾次並沒有再出現ora-02074, 也有取到設定的EBS User ID.

後續測試活動再繼續觀察了...

PS.pragma autonomous_transaction;說明可參考其他:

https://tomkuo139.blogspot.com/2009/11/oracle-plsql-pragma-autonomoustransacti.html