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.
