Workflow 遇到 ORA-4061 / ORA-4065 / ORA-4068 的錯誤處理

在 Oracle ERP 中, 若更新 Workflow 運行用的 DB Package, 就會有可能出現這個錯誤,

其可能原因，如下：

1) When a package is compiled, all copies in the shared pool are flagged as invalid. The error ORA-04061 in the error stack is caused because the notification mailer is reading an old version of the package stored in memory.

2) If a stateful package is instantiated in a particular session, and then becomes invalidated, then all further references to that package in PL/SQL executed from SQL can result in an ORA-4061/ORA-4065 error message pair. Contrast this with the behavior if PL/SQL is invoked directly (not from SQL): a single set of ORA-4061/ORA-4065/ORA-4068 errors is given on the first reference; and subsequent references get no errors.

解決方式：

方式 1) 清空 Shared pool, 如下 :

程式碼

begin
  sys.dbms_shared_pool.unkeep( 'Owner.Object_Name', 'P' );
  dbms_output.put_line( 'Clear OK' );
exception
  when others then
    dbms_output.put_line( 'Error : ' || SQLERRM );
end;

方式 2) 若上述方式不可行，則執行此程式, 如下 :

 程式碼

DBMS_SESSION.RESET_PACKAGE;

方式 3) 若上述方式還不行，則 Apply RDBMS patch 2747350 on top of database version 9.2.0.5.

希望對被此問題困擾的人, 有所幫助.