ORA-04068: existing state of packages has been discarded

Exploring ORA-04068 and ORA-04065

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.

Joins in Oracle – 101

For anyone who is starting with relational databases (using for example Oracle) understaning Table Joins (or result sets) is one of the most daunting and confusing concept. Nevertheless, Join operation is a fundamental operation in RDBMS.In this writeup I’ll attempt to describe a simple, empircal approach to get an understaning of joins in relational database. Specifically I’ll use Oracle as platform, forI believe the join constructs supported in Oracle is fairly comprehensive (even for theoritical concepts).
Available online documentation on Table Joins in relational database is exhaustive.
The approach (and sample code) included in this writeup eliminates any implicit meaning and lets you concentrate on RDBMS implmentation of Table Join operation.

If you have access to Oracle DBMS, you can cut n’ paste the code samples and give it try and get a feel!

Required setup

We’ll set up few tables and sample data for our experiments…

-- SETUP for examples... 
drop table A; 
drop table B; 
drop table C;   
create table a (ca int, sa varchar2(50)); 
create table b (cb int, sb varchar2(50)); 
create table c (cc int, sc varchar2(50));   
insert into A 
select * from ( select level myid, to_char( to_date( level,'J'),'Jsp') mystr 
from dual connect by level < 11 ); 
commit;   
insert into B select * from ( select level myid, to_char( to_date( level,'J'),'Jsp') mystr 
from dual connect by level < 21 ) where mod(myid,2) = 0; 
commit;   
insert into C 
select * from ( select level myid, to_char( to_date( level,'J'),'Jsp') mystr 
from dual connect by level < 11 ) where myid < 6; 
commit;

Let us verify the data, we have setup.

-- Verify the setup 
select * from A; 
select * from B; 
select * from C; 

Now let us explore the various JOIN operations and their results in Oracle.
Note: These operations are very similar in other commercial databases like DB2, MySQL etc.
R1: To select rows from table A and matching rows from table B.

-- ************************************************************** 
-- Requirement EQUI JOIN 1. (AKA INNER JOIN) 
-- To select rows from A and MATCHING rows from B 
-- use A JOIN B or B JOIN A  
-- 
-- ANSI SQL syntax 
select * from A JOIN B on (A.CA = B.CB) ; 
-- **** OR **** 
select * from B JOIN A on (A.CA = B.CB) ;
-- OR Traditional Oracle syntax 
select * from A, B where A.CA = B.CB ; 

R2: To select rows from A and MATCHING rows from table B and subsequent MATCHING rows from table C.

-- ANSI SQL syntax 
select * from A JOIN B on (A.CA = B.CB) JOIN C on(B.CB = C.CC) ; 
-- OR Traditional Oracle syntax 
select * from A, B, C 
where A.CA = B.CB and B.CB = C.CC; 

R3: Requirement
OUTER JOIN 1.
To select ALL rows from A and ONLY MATCHING rows from B use A LEFT OUTER JOIN B or B RIGHT OUTER JOIN A. The columns for table B will have NULL value where a match for A is not found.

-- ANSI SQL syntax 
select * from A LEFT OUTER JOIN B on (A.CA = B.CB); 
-- **** OR **** 
select * from B RIGHT OUTER JOIN A on (A.CA = B.CB); 
-- OR Traditional Oracle syntax 
select * from A, B where A.CA = B.CB(+); 

The queries above are EQUIVALENT – return the same result set. However note the “order” in which the rows are returned by default.
OUTER JOIN 2.
To select ALL rows from B and ONLY MATCHING rows from A use B LEFT OUTER JOIN A or A RIGHT OUTER JOIN B. The columns for table A will have NULL value where a match for B is not found.

-- ANSI SQL syntax 
select * from B LEFT OUTER JOIN A on (A.CA = B.CB); 
-- **** OR **** 
select * from A RIGHT OUTER JOIN B on (A.CA = B.CB); 
-- Traditional Oracle syntax 
select * from A, B where A.CA(+) = B.CB; 

OUTER JOIN 3.
To select ALL rows from A and ONLY MATCHING rows from B AND select those rows from C that match rows from B

-- ANSI SQL syntax
select * from A LEFT OUTER JOIN B on (A.CA = B.CB)LEFT OUTER JOIN C on (B.CB = C.CC);
-- OR Traditional Oracle syntax
select * from A, B, C where A.CA = B.CB(+) and B.CB = C.CC(+);

In addition to INNER and OUTER (LEFT and RIGHT) Joins, Oracle also provides (in ANSI extension) FULL OUTER join.
Note: FULL OUTER join operation can be accomplished using traditional SQL (without ANSI extensions).
R4: To select ALL rows from A and ALL rows from B, however match the rows from A and B where possible.

AWS Oracle 11g instance

After logging into AWS, I chose the the public ami-47c5252e (Oracle published AMI) and started the instance. Instance started successfully.
Public DNS: ec2-184-72-84-130.compute-1.amazonaws.com

Save and generate the public key using puttygen

Download and save the *.ppm file from AWS instance. Use puttygen.exe to load and create private key. Puttygen does not recognize the .ppm extension, so change the filetype and then open .ppm file and save it as .ppk extension.
Using putty.exe initiated the telnet session to my instance.
Logged in as ROOT and installed the Oracle version 11g using preconfigured DBCA.
To enable password based login to the instance, edit the sshd_config file and restart ssh deamon

/etc/ssh
$ vi sshd_config
/etc/ssh
$ /etc/init.d/sshd restart
Stopping sshd: [ OK ]
Starting sshd: [ OK ]
/etc/ssh