Ora 06508 ошибка

When a user submits a new order, sometimes will get an error:

Error in Line 1.0: ORA-06508:PL/SQL:could not find program unit being called in Package OM_TAX_UTIL Procedure Tax_Line

This error is persistent and relogin does not allow the user to bypass the error.
This framework uses Java Apache processes to connect to the DB, and not the default connection method, seen when a form opens a session and closes it when it is done.
These Apache processes stay connected to the DB, until an Apache restart/shutdown occurs.

We enable tracing for ORA-06508:

ALTER SYSTEM SET EVENTS '6508 trace name errorstack level 3';

The trace files created by the «submit orders» attempts contain the following group of ORA errors:

ORA-04061: existing state of package body "APPS.ARP_PROCESS_TAX" has been invalidated

ORA-04065: not executed, altered or dropped package body "APPS.ARP_PROCESS_TAX"

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

In OM_TAX_UTIL.TAX_LINE there are calls to ARP_PROCESS_TAX’s procedures.
ARP_PROCESS_TAX package gets its body invalidated by a AR purge process, which executes DDL operations on a few AR interface tables.
This process recompiles every object it invalidates, before it finishes.
So, ARP_PROCESS_TAX spec and body have VALID status, when its procedures are called by TAX_LINE.
The key for this problem is the fact that the Apache processes that handle the Quoting requests, stay always connected to the database.
For some reason (probably Bug 2747350) the new package state is not picked up.

A demonstration is following to simulate the issue.
We create a table:

CREATE TABLE SYSTEM.TEST_TABLE
TABLESPACE TOOLS
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING
AS
   SELECT * FROM DBA_OBJECTS;

A view on this table:

CREATE OR REPLACE FORCE VIEW SYSTEM.TEST_VIEW
(
   OWNER,
   OBJECT_NAME,
   SUBOBJECT_NAME,
   OBJECT_ID,
   DATA_OBJECT_ID,
   OBJECT_TYPE,
   CREATED,
   LAST_DDL_TIME,
   TIMESTAMP,
   STATUS,
   TEMPORARY,
   GENERATED,
   SECONDARY
)
AS
   SELECT "OWNER",
          "OBJECT_NAME",
          "SUBOBJECT_NAME",
          "OBJECT_ID",
          "DATA_OBJECT_ID",
          "OBJECT_TYPE",
          "CREATED",
          "LAST_DDL_TIME",
          "TIMESTAMP",
          "STATUS",
          "TEMPORARY",
          "GENERATED",
          "SECONDARY"
     FROM TEST_TABLE;

A package which uses this view, and is equivalent to ARP_PROCESS_TAX:

CREATE OR REPLACE PACKAGE TEST_PKG
IS
   X   CONSTANT NUMBER := 1;
   GVAR         VARCHAR2 (50);

   FUNCTION GETINFO
      RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY TEST_PKG
IS
   FUNCTION GETINFO
      RETURN VARCHAR2
   IS
   BEGIN
      SELECT OBJECT_NAME
        INTO GVAR
        FROM TEST_VIEW
       WHERE OBJECT_ID = 100;
      RETURN GVAR;
   END;
END;
/

A package which calls test_pkg.getInfo and has exception handling of ORA-6508 error.
This is equivalent to the OM_TAX_UTIL package:

CREATE OR REPLACE PACKAGE TEST_PKG_RUN
IS
   X   CONSTANT NUMBER := 1;

   PROCEDURE RUNPROC;
END;
/

CREATE OR REPLACE PACKAGE BODY TEST_PKG_RUN
IS
   PROCEDURE RUNPROC
   IS
      PACKAGE_EXCEPTION   EXCEPTION;
      PRAGMA EXCEPTION_INIT (PACKAGE_EXCEPTION, -6508);
      D                   VARCHAR2 (50);
   BEGIN
      D := TEST_PKG.GETINFO ();
      DBMS_OUTPUT.PUT_LINE (D);
      DBMS_LOCK.SLEEP (10);           --> to allow me time to recreate package
   EXCEPTION
      WHEN PACKAGE_EXCEPTION
      THEN
         DBMS_OUTPUT.PUT_LINE ('Called failed with ' || SQLCODE);
   END;
END;
/

We open one session [1] and run test_pkg_run.runproc:

SQL> conn system@sme_gnvdev
Enter password: *******
Connected.

SQL> set serveroutput on
SQL> exec test_pkg_run.runproc;
I_IDL_UB11
PL/SQL procedure successfully completed.

We open a second session [2] and modify test_view, which invalidates test_pkg’s body:

SQL> conn system@sme_gnvdev
Enter password: *******
Connected.

SQL> CREATE OR REPLACE FORCE VIEW SYSTEM.TEST_VIEW
2 (
3 OWNER,
4 OBJECT_NAME,
5 SUBOBJECT_NAME,
6 OBJECT_ID,
7 DATA_OBJECT_ID,
8 OBJECT_TYPE,
9 CREATED,
10 LAST_DDL_TIME,
11 --TIMESTAMP,
12 STATUS,
13 TEMPORARY,
14 GENERATED,
15 SECONDARY
16 )
17 AS
18 SELECT "OWNER",
19 "OBJECT_NAME",
20 "SUBOBJECT_NAME",
21 "OBJECT_ID",
22 "DATA_OBJECT_ID",
23 "OBJECT_TYPE",
24 "CREATED",
25 "LAST_DDL_TIME",
26 -- "TIMESTAMP",
27 "STATUS",
28 "TEMPORARY",
29 "GENERATED",
30 "SECONDARY"
31 FROM test_table;
View created.

SQL> select object_type,status
2 from dba_objects where object_name='TEST_PKG';

OBJECT_TYPE STATUS
------------------
PACKAGE VALID
PACKAGE BODY INVALID

In [2] we compile test_pkg’s body and validate it:

SQL> alter package test_pkg compile body;
Package body altered.

SQL> select object_type,status
2 from dba_objects where object_name='TEST_PKG';

OBJECT_TYPE STATUS
------------------
PACKAGE VALID
PACKAGE BODY VALID

In [1] any execution of test_pkg_run.runproc results to a ORA-6508 error:

SQL> set serveroutput on
SQL> exec test_pkg_run.runproc;
Called failed with -6508
PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> exec test_pkg_run.runproc;
Called failed with -6508
PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> exec test_pkg_run.runproc;
Called failed with -6508
PL/SQL procedure successfully completed.

Oracle has filed this issue under Bug 8613161: PRAGMA EXCEPTION_INIT MASKS ORA-4068 AND PACKAGE IS NOT RE-INSTANTIATED PROBLEM:

 1. Clear description of the problem encountered: 


A PL/SQL package declares a user defined exception using PRAGMA EXCEPTION_INIT for ORA-6508. When this error occurs a second user defined  exception is raised which appears to mask the underlying ORA-4068 error which  accompanies the ORA-6508 error. This has the effect of preventing the Package from being re-instantiated in the session even though the underlying  cause of the ORA-6508 error (an invalid dependent) is resolved.  


    procedure runproc is 
      package_exception exception; 
      rzy_except exception; 
      PRAGMA EXCEPTION_INIT (package_exception, -6508); 
      PRAGMA EXCEPTION_INIT (rzy_except, -20001); 
      d varchar2(50); 
    begin 
      d:=test_pkg.getInfo(); 
      dbms_output.PUT_LINE(d); 
      dbms_lock.sleep(10); —> to allow me time to recreate package 
    exception 
      when package_exception then 
      dbms_output.put_line(‘Called failed with ‘||sqlcode); 
      —raise; 
      raise rzy_except; 
    end; 


If the raised user defined exception (raise rzy_except;) is replaced with the raise statement, the ORA-4068 error is shown on the error stack and the package is re-instantiated in the session. 


I have raised this bug as a P2 because the APPS customer has coded a great deal of customisation code before encountering this error and it is not feasible for them to make the necessary code changes.  


=========================     
DIAGNOSTIC ANALYSIS: 
=========================    
WORKAROUND: 
Use the raise statement or reconnect to the db. 
=========================    
RELATED BUGS: 
Bug 229349 ORA-4068 LEADS TO INSERT ALWAYS FAILING IF TRIGGER USES RAISE_APPLICATION_ERROR. 
=========================    
REPRODUCIBILITY: 
 1. State if the problem is reproducible; indicate where and predictability Reproduces every time 
 2. List the versions in which the problem has reproduced   On Solaris Oracle Version 10.2.0.4, 11.1.0.7 
 3. List any versions in which the problem has not reproduced . 
=========================     
tc.sql — creates table, view and package. 
tc1.sql — runs the package 
tc2.sql — drops and recreates the view and checks the status of the package in user_objects. 
Two sessions A and B  
1. Run tc.sql to set up the table, view and package. 
2. Run tc1.sql to run the package and straight after in session B run tc2.sql 
Session A 
SQL> @tc.sql 
SQL> @tc1.sql 
Session B 
SQL> @tc2.sql 


Results: 
ERROR at line 1: 
ORA-20001: 
ORA-06512: at «SCOTT.TEST_PKG_RUN», line 17 
ORA-04061: existing state of package body «SCOTT.TEST_PKG» has been invalidated 
ORA-04065: not executed, altered or dropped package body «SCOTT.TEST_PKG» 
ORA-06508: PL/SQL: could not find program unit being called: «SCOTT.TEST_PKG» 
ORA-06512: at line 1 


This error occurs every time even though the package is now valid, which can be shown in session B. 
When ‘raise rzy_except;’ is commented out and replaced by ‘raise;’, the package reports the following error: 


ERROR at line 1: 
ORA-04068: existing state of packages has been discarded 
ORA-04061: existing state of package body «SCOTT.TEST_PKG» has been  invalidated
ORA-04065: not executed, altered or dropped package body «SCOTT.TEST_PKG» 
ORA-06508: PL/SQL: could not find program unit being called: «SCOTT.TEST_PKG» 
ORA-06512: at «SCOTT.TEST_PKG_RUN», line 16 
ORA-06512: at line 1 


The following call to the package works as expected as the ORA-4068 error triggers an in-instantiation of the package. 
This is not a bug.  The only way to force it to clear the package state, recompile the package and load the new instantiation is to allow the ORA-4068 to be raised back to the client.  By trapping it you are signalling to Oracle that you do not want this to happen yet.   


Apart from changing their code the only other option is to use event 10945  that reverts behaviour to 8i so that it will not raise the error at all but carry on using the old copy of the package.  This also means that the package  does not get recompiled on the next call to it in that session.


I would not generally recommend customers do this as, if for instance the application uses connection/session pooling, it could be that some sessions run for a long time using an out of date copy of a package.  


The event can be set at session level via: 
alter session set events = ‘10945 trace name context forever, level 1’; 


but they’d probably need it set system wide.   


Note, this event only works as long as the old instantiation exists.  If a new session calls the same currently invalid package and therefore recompiles it automatically, or an alter compile is issued, then the event will have no effect on the existing session and the errors will be raised again.   


Using the testcase, with the event set in tc1.sql and the alter compile commented out of tc2.sql the package remains invalid and no errors occur.   


Run the alter compile though and the errors will appear.