Since Oracle version 7.3 (probably even before that) ORA-04068 was encountered by developers and database administrators in PL/SQL packages. In this document I’ll attempt to de-mystify this error by exploring what the error means, its purpose and various causes and potential solutions.
One of the main reasons for mystery and frustration with this error is the fact that the package encountering the error can be very different from the object that actually caused the error. The package encountering ORA-04068 is dependent on the object causing the error. This dependency can be immediate (if we are fortunate) or could be arbitrarily long list of database objects. If we are really unfortunate the dependency can be also remote.
Fortunately, this error will resolve itself, upon re-invocation of the package that encountered the error.
There are several potential solutions/workarounds for this error available on the internet websites and blogs. Some of these recommendations are incorrect or dangerous.
As the new adage goes: Be careful of what you learn from Internet ;-)
Introduction to the error ORA-04068
Typically ORA-04068 is followed by ORA-04065 and ORA-06508, as shown below.
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "pkg_c"
ORA-06508: PL/SQL: could not find program unit being called: "pkg_c"
|
This error is raised by PL/SQL engine upon on invocation of a package which maintains state and whose existing state is destroyed by some action. In such situation the PL/SQL engine is obligated to inform the client (invoker of the package) by raising exception ORA-04068.
Package State Maintenance
A PL/SQL package once invoked in a session will be resident (in the memory) through the life of the session. Specifically if the package has package level variables defined. The package level variables can be in the SPEC or in the BODY. The package will retain the values assigned to these variables for the life of the session.
When a package, say P, invoked in a session (say session 1) is maintaining state and any object that package P depends on is modified, compiled, then the next invocation of package P in session 1 will raise ORA-04068.
DEMO of ORA-04068
In this section, I’ll walk though the creation and some apparent solutions to this error. For simplicity, I will not address remote package dependencies in this document. (May be separate document)
Setup the environment for walk-though
For this section of DEMO, I’ll use a table MYT and two dependent packages. I’ll use only package specifications (and not bodies) for keeping the examples small. Package PKG_P depends on package PKG_C which in turn depends on table MYT.
Session A>drop table myt;
Table dropped.
Session A>drop package pkg_c;
Package dropped.
Session A>drop package pkg_p;
Package dropped.
Session A>create table myt (col1 int);
Table created.
Session A>CREATE OR REPLACE PACKAGE pkg_c
2 AS
3 TYPE t1 IS ARRAY (3) OF NUMBER;
4 rc myt%rowtype; ---- <<<< Dependent on table MYT
5 x1 NUMBER;
6 y1 NUMBER;
7 END;
8 /
Package created.
Session A>CREATE OR REPLACE PACKAGE pkg_p
2 AS
3 x NUMBER;
4 y NUMBER;
5 j pkg_c.t1; ---- <<<< Dependent on package PKG_C
6 END;
7 /
Package created.
Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.
Session A>
|
Scenario 1: Package PKG_C recompiled
Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.
Session A>alter package pkg_c compile;
Package altered.
Session A>exec pkg_P.Y := 2;
BEGIN pkg_P.Y := 2; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "PKG_P" has been invalidated
ORA-04065: not executed, altered or dropped package "PKG_P"
ORA-06508: PL/SQL: could not find program unit being called: "PKG_P"
ORA-06512: at line 1
Session A>exec pkg_P.Y := 3;
PL/SQL procedure successfully completed.
Session A>
|
Subsequent execution of the package in same session does not raise this error, as the state of the package has been reset.
When PKG_C is recompiled, Oracle invalidates all dependencies. Subsequent invocation of PKG_P results in ORA-06508 and auto-compile. This recompilation results in package state of PKG_P being discarded, resulting in ORA-04068.
Scenario 2: Package PKG_P recompiled
However recompilation of package PKG_P does NOT result in error in the same session. I believe after compilation of the package, Oracle resets state of the package in that session.
Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.
Session A>alter package pkg_p compile;
Package altered.
Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.
Session A>exec pkg_P.Y := 3;
PL/SQL procedure successfully completed.
Session A>
|
Scenario 3: Recreate or Alter the Table MYT
Similar to Scenario 1, described above, all the dependent packages on the object being altered are invalidated. Next invocation will result in auto-compile and if there is an existing state of the package, then it will result in ORA-04068.
DROP and CREATE
Session A>;
exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.
Session A>;drop table myt;
Table dropped.
Session A>create table myt (col1 int);
Table created.
Session A>exec pkg_P.Y := 2;
BEGIN pkg_P.Y := 2; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package “PKG_P” has been invalidated
ORA-04065: not executed, altered or dropped package “PKG_P”
ORA-06508: PL/SQL: could not find program unit being called: “PKG_P”
ORA-06512: at line 1
Session A>exec pkg_P.Y := 3;
PL/SQL procedure successfully completed.
Session A>
Session A>
|
ALTER Table
Session A>exec pkg_P.Y := 2;
PL/SQL procedure successfully completed.
Session A>alter table myt add (col2 int);
Table altered.
Session A>exec pkg_P.Y := 2;
BEGIN pkg_P.Y := 2; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "PKG_P" has been invalidated
ORA-04065: not executed, altered or dropped package "PKG_P"
ORA-06508: PL/SQL: could not find program unit being called: "PKG_P"
ORA-06512: at line 1
Session A>exec pkg_P.Y := 3;
PL/SQL procedure successfully completed.
Session A>
|
1. For a package to maintain state, it must have package level variables declared.
2. Other connected sessions that may have invoked the package will also encounter this error on first invocation, after the package is recompiled.
Scenario 4: Package with package level constants versus variables
In this demonstration, I’ll use two different sessions.
Potential Solutions
I’ll describe some of the solutions and workarounds suggested on the internet for ORA-04068.
PRAGMA SERIALLY_REUSABLE
The PRAGMA directive SERIALLY_REUSABLE will definitely eliminate this error from occurring.
HOWEVER, this approach undermines the the objective of the package level variables.
No comments:
Post a Comment