[Oracle]DB Link Session ORA-04068

 APEX DB 透過DB LINK呼叫EBS Package,

出現幾個怪現象:

1. 

ERROR at line 1:

ORA-04068: existing state of packages has been discarded

ORA-04061: existing state of package body "PACKAGE.NAME" has been

invalidated

ORA-06508: PL/SQL: could not find program unit being called:

"PACKAGE.NAME"

ORA-06512: at line 1

2. EBS Package 裡有使用  fnd_global.org_id, fnd_global.user_id, FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')

所以APEX DB執行前會去set_client_info, fnd_global.apps_initialize.

但執行EBS package 寫入EBS table卻出現別人的user_id & org_id.

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

先是懷疑DB Link的session, 查到這篇說明

https://stackoverflow.com/questions/1761595/frequent-error-in-oracle-ora-04068-existing-state-of-packages-has-been-discarde

關鍵字: 

ALTER SESSION CLOSE  DATABASE LINK DBLINK

PRAGMA SERIALLY_REUSABLE

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

ALTER SESSION CLOSE  DATABASE LINK DBLINK:

https://www.uj5u.com/shujuku/28126.html

說明一般企業不使用DB Link原因:不外乎出問題不好查..佔資源...

那如果真的要使用, 該如何管理DB Link Session?

文中有三種方式, 個人認為1和3比較適合, 1為首選

1. 使用完後程式commit, close db link (沒有commit會出現ora-02080錯誤)

指令:

commit;

alter sesssion close database link ;

or 系統包:

DBMS_SESSION.CLOSE_DATABASE_LINK(dblink_name);

EXAMPLE:

DECLARE

  v VARCHAR2(50);

CURSOR r IS

  SELECT name FROM employee@test_db_link WHERE empno = '1';

BEGIN

OPEN r;

    LOOP

FETCH r INTO v;

EXIT WHEN r%NOTFOUND;

    END LOOP;

    CLOSE r;

    COMMIT;

    EXECUTE IMMEDIATE 'alter session close database link test_db_link';

END;

3.定期JOB Kill session

select sid,

 serial#,

 username,

 osuser,

 machine,

 'alter system kill session ' || '''' || sid || ',' || serial# ||''' immediate;' session_level

 from v$session

where machine in ('連線的DB machine1', '連線的DB machine2', '連線的DB machine3');

命令後面要加immediate, 沒加的話v$session中的STATUS列會變為KILLED，但是資源並未釋放

利用方式三可以做成JOB定時kill session，但這樣存在很大的風險。 ==> 可能不小心刪到還在使用的session?? 而且我們沒有EBS DB權限

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

PRAGMA SERIALLY_REUSABLE

https://blog.csdn.net/xiadingling/article/details/82492709

"Packaged public variables and cursors persist for the duration of a session. 

They can be shared by all subprograms that execute in the environment. 

They let you maintain data across transactions without storing it in the database."

Package裡的變數生命週期是會話(session), 所以如果在同一session執行多次 變數可能會使用到上一次執行的值.

PRAGMA SERIALLY_REUSABLE這命令使得Oracle只保持一次呼叫的值.

使用時Package & Package Body都要宣告.

缺點:每次使用都會釋放, 也因此會占用DB內存, 依照使用次數成長, 與登錄用戶數無關, 會大量使用到內存

Oracle PLSQL Call API

 

Ajax Callback:  呼叫DB Funcction取得值

Process_name=set_clock_in

declare

  v_clock_in varchar2(20); 

begin

  SELECT get_clock_in_fun( to_char(sysdate,'yyyy-mm-dd')) 

  into v_clock_in

  from dual;

  htp.p(v_clock_in);

end;

Javascript: AJAX Call API

apex.server.process("set_clock_in" //Process or AJAX Callback name

                    , null          //Parameter

                    , {  

                       success: function(pData){

                           apex.item("P1_CLOCK_IN").setValue(pData);

                           //alert('Record loaded successfully');

                        },

                        dataType: "text"

                    }

                   );

PLSQL Call API Function:

get_clock_in_fun

CREATE OR REPLACE function get_clock_in_fun( v_date IN varchar2)

return varchar2

is 

      -- Local variables here

      i      integer;

      l_resp clob;

      g_url  varchar2(300) := 'https://example-api.company.com/clock-in/v_date;

      v_hashkey  varchar2(128) := '.........................................................................';

      v_hashdate varchar2(20);

      v_token    varchar2(200);

      v_resp varchar2(4000);

     

      web_failure_detected EXCEPTION;

      PRAGMA EXCEPTION_INIT(web_failure_detected, -29273);

begin

    --Authorization : {Hash Key}:{UTC datetime} 

    select to_char(systimestamp at time zone 'UTC','yyyymmddhh24miss') 

    into v_hashdate

    from dual;

      

    select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_hashkey||':'||v_hashdate))) 

    into v_token

    from dual;

    --utl_encode.base64_encode()編碼生成的字串以64個字元為一組其後添加回車換行字元

    --利用正則式替換掉所有空白字元,不管是否為回車換行字元

    v_token:=regexp_replace(v_token, '\s', '');

           

    apex_web_service.g_request_headers(1).name := 'Authorization';

    apex_web_service.g_request_headers(1).value := 'Bearer '||v_token;

      

    -- Content-Type

    apex_web_service.g_request_headers(2).name := 'Content-Type';

    apex_web_service.g_request_headers(2).Value := 'text/html';

    --HTTP 411 header加入Content-Length

    apex_web_service.g_request_headers(3).name := 'Content-Length'; 

    apex_web_service.g_request_headers(3).value := 0; 

    

    l_resp := apex_web_service.make_rest_request(p_url         => g_url,

                                                 p_http_method => 'GET'

                                                   --p_parm_name  => apex_util.string_to_table('',':'),

                                                   --p_parm_value => apex_util.string_to_table('',':')

                                                   --p_wallet_path =>null,

                                                   --p_wallet_pwd  => null

                                                 );

    v_resp := substr(l_resp, 1, 4000);

    with date_array (col) as

   (select v_resp

       from dual)

    SELECT min(val) INTO v_resp FROM 

    (

select regexp_substr(replace(substr(col,instr(col,'[')+1, instr(col,']')-2), '"', ''), '[^,]+', 1, level) val

from date_array

connect by level <= regexp_count(col, ',') + 1

    );

    

    return v_resp;

EXCEPTION 

    WHEN web_failure_detected THEN

       DBMS_OUTPUT.put_line ('web failure detected');

       RETURN ':';

    WHEN OTHERS THEN 

       RETURN '--';

END;