Dealing with Oracle PL/SQL Error "ORA-04068: existing state of packages has been discarded" Transparently in Java/JDBC

My next article is on how to deal with the infamous "ORA-04068" error that occurs when using PL/SQL packages in your application. I first wrote this article using the freely available AlleyCode software that allows you to edit HTML. I then posted it on this blog - even after many re-edits directly on the blog itself, the article as displayed on the blog does not look very elegant. This is especially true for the Java code which mysteriously keeps loosing the indentation at places. Overall, I still think it is just about readable and hope you would enjoy it:)

Please give your comments on the blog.

Thank you!

Introduction

One of the most critical arguments I have made in my book Expert Oracle JDBC Programming is that, we should strive to use stored procedures wherever we need to interact with databases - regardless of which database we use. This assumes that the database does provide the facility to write stored procedures which is true for most of the major databases such as Oracle, MySQL and SQL Server. This is true whether you use Java, .NET or any other language or framework of your choice. The arguments for and against this opinion are detailed in my book - I won't repeat them here though I may write a future article on this topic.

In Oracle, of course, if you want to write stored procedures, you should use PL/SQL packages. In this article, I assume that you are familiar with PL/SQL in general and PL/SQL packages in particular. This article focuses on one particular "infamous" error that surprises and annoys many developers who use PL/SQL and invoke it from application layers using APIs such as JDBC. That error is "ORA-04068: existing state of packages has been discarded". This error is raised when Oracle thinks that your package state has become invalid for some reason. In this article we will discuss:

1. what "ORA-04068" error is and why it occurs,
   
2. what its impact could be, and
   
3. the proposed solution (refined iteratively)

Let us begin by defining what "ORA-04068" error is in the next section.

Note: We use Oracle 9.2.0.3 in our examples though the same concepts should be applicable to Oracle 10g as well.

What is "ORA-04068" Error and Why it Occurs?

If we look at the definition of ORA-04068 using the oerr program provided by Oracle, we get the following information:

$oerr ora 04068
04068, 00000, "existing state of packages%s%s%s has been discarded"
// *Cause:  One of errors 4060 - 4067 when attempt to execute a stored procedure.
// *Action: Try again after proper re-initialization of any
//          application's state.

The error indicates that the existing state of the package that is being executed has been invalidated by an action taken by another session. The "state" refers to any global variables (including constants) that the package may have declared in the specification or body. The action that causes this error typically would be (but not restricted to) recompilation of the package after the connection for the session in which the error occurs was obtained. The action advised by Oracle is to re-try after re-initializing the application state appropriately to adjust for the new state of the package.

All of this would hopefully become much clearer once we look at some examples.

Consider the table t defined as follows:

create table t (x number );

Consider the specification of the package called pkg with a single procedure named p as follows:

create or replace package pkg as
procedure p;
end pkg;
/

The package body for the package pkg shown below defines the procedure p which just inserts a constant value of 1 into the table t we defined earlier.

create or replace package body pkg as
procedure p
is
begin
insert into t(x) values (1);
end p;
end pkg;
/

Note that there are no global variables or constants in either the package specification or body. In other words, the package is "stateless".

We will first illustrate the concept by using two SQL*Plus sessions. In each "experiment", we would try to execute the procedure pkg.p after we have recompiled the package body in another session. Let us start with Experiment 1 where we would actually not get the ORA-04068 even though the package body has been recompiled in another session. This is because the package is "stateless" in that it does not have any global variables or constants defined in the specification or body.

Experiment 1

Assume that the table t and the specification and body of the package pkg have already been created in the package.
In SQL*Plus session 1, we execute the package and get the following results (the package executes successfully.)

Note: You may note that the SQL*Plus prompt in this article is sometimes different than the regular prompt ("SQL >") - for example, it is "session 1" in the code below. This can be achieved by using the command "set sqlprompt 'session 1'", for example.

session 1> exec pkg.p

PL/SQL procedure successfully completed.

In SQL*Plus session 2, we recompile the package by recreating it as follows:

session 2> create or replace package body pkg as
2    procedure p
3    is
4    begin
5      insert into t(x) values (1);
6    end p;
7  end pkg;
8  /

Package body created.

session 2> show errors;
No errors.

Now if you go back to session 1 and re-execute the package procedure p, it works just fine.

session 1> exec pkg.p

PL/SQL procedure successfully completed.

Let us recap what we did so far. We defined a simple package with just one procedure that inserts a constant into a table. We started a session and executed the package procedure. In another session we recompiled the package (by recreating it.) When we re-execute the package in the first session, it worked fine - in particular, the recompilation of the package in session 2 resulted in no error in the second execution of the procedure in session 1.

Let us now repeat the whole experiment with just one change - we add a global constant to the package body (adding it to the specification would work just the same.) This means that we added "state" to the package. We illustrate the same experiment with this single change in our next section titled "Experiment 2".

Experiment 2

We log off from our earlier sessions. We start a new session and compile our new package body in session 1 as shown below - note that there is a constant declaration in the beginning of the package shown in bold. This is the state of the package. This constant is not being used anywhere but that is of no consequence to the results of this experiment.

session1>@pkg_body
session1>create or replace package body pkg as
2    g_constant constant number := 1;
3    procedure p
4    is
5    begin
6      insert into t(x) values (1);
7    end p;
8  end pkg;
9  /

Package body created.

session1>show errors;
No errors.

Now we execute the procedure p in session 1.

session 1> exec pkg.p

PL/SQL procedure successfully completed.

We start a new session "session 2" and recompile the package by recreating it.

session 2> @pkg_body
session 2> create or replace package body pkg as
2    g_constant constant number := 1;
3    procedure p
4    is
5    begin
6      insert into t(x) values (1);
7    end p;
8  end pkg;
9  /

Package body created.

session 2> show errors;

We execute the procedure p again in the session 1 and get the following results:

session1>exec pkg.p
BEGIN pkg.p; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "ORA92.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "ORA92.PKG"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1

What happened? When we recompiled the package body in session 2, we reset the state of the package. In other words, for any session that had connected before the package compilation, the existing state of the package (defined, in this case, by the value assigned to the constant in the package body) was wiped out from the memory. Note that we actually did not change the state (we retained the same value of the constant while recompiling) but Oracle does not track details at that level. As far as Oracle is concerned, in session 2, someone recompiled the package pkg - the package had a state which is now reset to a "new" state - so for any existing session that was already connected to Oracle before this recompilation happened, the package state becomes invalid. Hence on the next execution of any procedure or function of the package, we get ORA-04068 error the very first time.

What happens if we re-execute the package in session 1 after we got the ORA-04068 in our first attempt? Let us see.

session 1> exec pkg.p

PL/SQL procedure successfully completed.

As you can see the next execution assumes that the calling application (in this case SQL*Plus) has adjusted to the new state (since Oracle informed it once of the changed state) and is re-executing the package with the new state of the package. That is what Oracle's suggested action is (see the beginning of this section):

Try again after proper re-initialization of any application's state.

The next section highlights some of the impact of the ORA-04068 error.

The Impact of "ORA-04068" Error

To gauge the impact of ORA-04068, all you have to do is google it. Two of the main impacts are as follows.

1. Most enterprise applications use connection pool in which connections are cached. Now whenever a new package definition has to be deployed, it needs to be recompiled in production. The moment you do that, for all the connections in the connection pool, the state of this package would be invalidated since the package was re-compiled after the connection was obtained (as part of the connection pool initialization sometime earlier.) Note that this is true regardless of whether you changed the state or not, whether you even changed the code or not. The very first time a procedure or function in this package is invoked, it would fail with the "ORA-04058" error. So typically, you have to remember to "flush" the connection pool (meaning discard existing connections and obtain new connections to Oracle.) This typically leads to a downtime in the application deployment. For example, if you are using tomcat and a connection pool within tomcat, you may have to bring down tomcat and bring it back up - so that it re-initializes the connection pool. What if a long-running batch is using one of the connections to execute some logic completely independent of the package that needs to be recompiled? You would either have to wait till the batch completes or kill it during deployment so you can re-initialize the connection pool. As you can imagine, this can be a nightmare in terms of availability of the application.
   


2. One of the even nastier impacts is that the developers are frustrated and confused by why a simple recompilation of the package (with a state) should result in Oracle failing with this error. This is especially true since other databases such as SQL Server and MySQL do not have the concept equivalent of packages and hence do not associate a state with stored procedures or functions. Hence, in these databases, you can redeploy stored procedures and expect the applications using them to work transparently. Whether this is the right choice or not on the part of other databases is debatable and is outside the scope of this article. Instead of understanding the underlying reason of ORA-04068 error and dealing with it, this single error can force the developers to abandon the use of stored procedures altogether (and thus abandon all the advantages that come with using stored procedures) and embed the SQL within their application code (such as code in Java.)
   

So What is the Solution?

In this section, we will discuss various solutions to deal with the "ORA-04068" error. Each solution would come with a set of constraints under which it is applicable. These solutions also show a progression of thoughts that would make the recommended solution and the trade-offs easier to understand.

Let us begin with solution 1.

Solution 1: Use Stateless Packages

One of the simplest solutions is to use only stateless packages in your system. As we illustrated in our earlier section, ORA-04068 does not occur when you re-execute a stateless package even after it was recompiled in another session. This is because there is no state that can be invalidated by Oracle in the first place.

This solution, though simple in theory, has the following significant drawbacks:

1. It precludes you from defining any state which may result in poor code. In general there are two types of state:
• A global variable: A global variable should be avoided in general anyway. I have yet to come across a justifiable need for declaring a global variable in a PL/SQL package or body.
  
• A global constant: Almost all significant production systems need constants to be defined. If you decree that constants are not allowed in your system, then it would lead to poor practice of duplicate values defined multiple times which when required to be changed would affect more than one part of the system thus reducing maintainability.
  
2. If you already have a system with packages that have state defined then this solution may result in a major rewrite. In such a case, you have to decide if the trade-off is worth it.
   

Let us move on to our next solution.

Solution 2: Move All Package State in a Different Package

The idea behind this solution is that we move any package state in the package body or package specification to another package which would refer to as the "companion state package". This means that we reduce the number of times we have to deal with "ORA-06068" since the packages themselves don't store any state though they are dependent on the companion package for their state. In my experience, most of the time the changes occur in the package body implementation - which would not result in an ORA-04068 if we implement this solution. The ORA-04068 still occurs if we recompile the companion state package.

Let us see this solution at work.

We create a new package called const as follows into which we move the constant that we defined earlier in the package body of our package pkg.

create or replace package const as
g_constant constant number := 1;
end const;
/
show errors;

Our package specification for the package pkg does not change and is repeated below for your convenience:

create or replace package pkg as
procedure p;
end pkg;
/
show errors;

The package body changes so that there is no constant defined in it any more (it was moved to the package const) and also the insert statement now uses the constant defined in the package const for getting the value. Thus the package pkg depends on the package const for its state as defined by the constant g_constant:

create or replace package body pkg as
procedure p
is
begin
insert into t(x) values (const.g_constant);
end p;
end pkg;
/
show errors;

Assume that we have altered the package definitions for the package pkg and also installed the new package const in our system. Now we login to our session 1 and execute the procedure - it executes successfully as expected:

session 1>exec pkg.p

PL/SQL procedure successfully completed.

We log in to session 2 and recompile the package specification and package body for the package pkg:

session 2>@pkg_spec
session 2>create or replace package pkg as
2    procedure p;
3  end pkg;
4  /

Package created.

session 2>show errors;
No errors.
session 2>@pkg_body
session 2>create or replace package body pkg as
2    procedure p
3    is
4    begin
5      insert into t(x) values (const.g_constant);
6    end p;
7  end pkg;
8  /

Package body created.

session 2>show errors;
No errors.

In session 1, when we re-execute the procedure it works fine even though we had recompiled the package specification and package body. This is because the state of the package is in the package const (which has not been recompiled) and hence has not changed when we recompiled the package pkg itself.

What happens when we recompile the package const though as follows in session 2?:

session 2>@const
session 2>create or replace package const as
2    g_constant constant number := 1;
3  end const;
4  /

Package created.

session 2>show errors;
No errors.

If we re-execute our package pkg in session 1, we get the ORA-04068 error as expected. The error clearly states that the package state in the package const has changed and hence the dependent package pkg has been invalidated.

session 1>exec pkg.p
BEGIN pkg.p; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "ORA92.CONST" has been invalidated
ORA-04065: not executed, altered or dropped package "ORA92.CONST"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "ORA92.PKG", line 5
ORA-06512: at line 1

Of course, if we re-execute the package in session 1 right after, it seems to work fine as expected:

session 1>exec pkg.p

PL/SQL procedure successfully completed.

Solution 2, though better than the solution 1, has the following drawbacks:

1. It requires you to always move the state of the package outside the package itself thus making the package state globally visible to all other packages in the system. In other words, you can not create variables (or constants) private to a package (if you declare a variable or constant in the package body, it can not be accessed by any other package - in this sense it is private to the package in which it is defined - this leads to better encapsulated code.) This weakens encapsulation within the system thus reducing maintainability of the system. In fact, if we go this route, we should only have constants as part of any package state (which, is an acceptable and even welcome self-imposed restriction.)
   
2. It requires you to move all the state of a package into a companion state package. This results in proliferation of companion packages in the system which makes this solution slightly inelegant. If you decide to have just one package with states of all other packages then you run into another problem wherein a single constant or variable change in the central package would invalidate all the other packages in the system - even those which are not dependent on the constant or variable. Only you can decide which of these two alternatives (central state package or companion state package per package) makes sense for you.
   
3. If you already have a system with packages that have state defined then this solution may be hard to implement since it may result in a major rewrite. In this case you have to weigh the consequences of continuous ORA-04068 errors with each deployment or a one-time rewrite of the system.

Our next set of solutions work towards an alternative that seem to be an improvement on the aforementiond two solutions but suffers from a drawback that makes the solution 1 or solution 2 as the final recommended solution for this article. However, I strongly suggest that you go through the next 2 solutions to understand the trade-offs and make your final judgement based on your knowledge of the system.

Solution 3: Detect the ORA-0408 Error and Re-execute the Package Procedure

This solution moves the burden of dealing with the error on the client. The idea is that Oracle, by generating the error ORA-04068, gives the client the information that the package state has been invalidated and it is up to the client to detect and react to this error. The client can choose to re-execute the procedure if that is what the client needs to do. We have already seen that this solution seems to work in SQL*Plus when the execution of the procedure right after the error works as expected. We will now see this in action in a Java program that uses JDBC and see whether this works or not.

First let us revert back to our old code where we had state in the package pkg itself. So we re-introduce the state in our package body as before - the code is reproduced below for your convenience:

create or replace package body pkg as
g_constant constant number := 1;
procedure p
is
begin
  insert into t(x) values (1);
end p;
end pkg;
/
show errors;

Assume that we have recompiled the package body so that we have the new code in place. We would first simulate in a Java program using JDBC, a situation which leads to ORA-04068 error. For this we will:

1. obtain a connection in the Java program using JDBC,
   
2. execute the pkg.p procedure in the Java program using JDBC,
   
3. sleep for some time (say 10 to 20 seconds) in the Java program,
   
4. while our Java program is sleeping, we recompile the package body of the package pkg in a separate SQL*Plus session,
   
5. re-execute the pkg.p procedure in the Java program using JDBC - this should result in the ORA-04068 error.

The Java program called ExecutePackageProcedureTwice is shown below. It executes the pkg.p procedure, sleeps for 20 seconds to give us enough time to re-compile the package to simulate a deployement, and then re-executes the procedure:

package dbj2ee.article2.design1;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleDriver;

public class ExecutePackageProcedureTwice {
public  static void main(String[] args) throws Exception {
Connection conn = null;
CallableStatement cstmt = null;
long sleepInSecs = 20;
try {
    conn = getConnection();
    cstmt = conn.prepareCall("{call pkg.p()}");
    executePkg(conn, cstmt);
    System.out.println("Sleeping for " + sleepInSecs + " seconds...");
    Thread.sleep(sleepInSecs*1000);
    System.out.println("Out of sleep...");
    executePkg(conn, cstmt);
} finally {
    try {
        if(cstmt != null)
            cstmt.close();
        if(conn != null)
            conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}
}
private static Connection getConnection() throws Exception {
DriverManager.registerDriver(new OracleDriver());
return DriverManager.getConnection("jdbc:oracle:thin:@hercdev:1521:hercdev", "hercules", "hercules");
}
private static void executePkg(Connection conn,  CallableStatement cstmt) throws Exception {
System.out.println("Executing the package...");
cstmt.executeUpdate();
conn.commit();
}
}

Let us now reproduce the ORA-04068 error.

Executing the class with appropriate CLASSPATH set to point to the class root directory and to the classes12.jar (Jar containing Oracle JDBC implementation), we get the following:

M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
\build\classes" dbj2ee.article2.design1.ExecutePackageProcedureTwice
Executing the package...
Sleeping for 20 seconds...

Once the Java program hits the point where it starts sleeping, we recompile the package in a separate SQL*Plus session:

SQL> @pkg_body
SQL> create or replace package body pkg as
2    g_constant constant number := 1;
3    procedure p
4    is
5    begin
6      insert into t(x) values (1);
7    end p;
8  end pkg;
9  /

Package body created.

SQL> show errors;
No errors.

Then after the Java program comes out of sleep, it fails with the ORA-04068 error as expected, when it tries to execute the package for the second time:

M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
\build\classes" dbj2ee.article2.design1.ExecutePackageProcedureTwice
Executing the package...
Sleeping for 20 seconds...
Out of sleep...
Executing the package...
Exception in thread "main" java.sql.SQLException: ORA-04068: existing state of p
ackages has been discarded
ORA-04061: existing state of package body "ORA92.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "ORA92.PKG"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.executeFetch(TTC7Protocol.java:955)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.ja
va:2053)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.jav
a:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme
nt.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePrepar
edStatement.java:589)
at dbj2ee.article2.design1.ExecutePackageProcedureTwice.executePkg(Execu
tePackageProcedureTwice.java:38)
at dbj2ee.article2.design1.ExecutePackageProcedureTwice.main(ExecutePack
ageProcedureTwice.java:20)

Now, as we said, we know at the client level (in this case in the Java program) via the Exception raised wherein we can detect the error code and respond by re-executing the package. The simplest implementation of this is shown in the modified program dbj2ee.article2.design2.ExecutePackageProcedureTwice - the differences from the first version are highlighted for your convenience. As you can see we catch the SQLException and check to see if the error is ORA-04068 - if so, we re-execute the package, otherwise we re-throw the exception.

package dbj2ee.article2.design2;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleDriver;

public class ExecutePackageProcedureTwice {
public  static void main(String[] args) throws Exception {
Connection conn = null;
CallableStatement cstmt = null;
long sleepInSecs = 20;
try {
conn = getConnection();
cstmt = conn.prepareCall("{call pkg.p()}");
executePkg(conn, cstmt);
System.out.println("Sleeping for " + sleepInSecs + " seconds...");
Thread.sleep(sleepInSecs*1000);
System.out.println("Out of sleep...");
executePkg(conn, cstmt);
} catch (SQLException e) {
            if(reExecutionRequired(e)){
                System.out.println("ORA-04068 detected - re-executing the package...");
                executePkg(conn, cstmt);
            } else
                throw e;
        } finally {
try {
if(cstmt != null)
cstmt.close();
if(conn != null)
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
private static boolean reExecutionRequired(SQLException e) {
        return "72000".equals(e.getSQLState()) && e.getErrorCode() == 4068;
    }
private static Connection getConnection() throws Exception {
DriverManager.registerDriver(new OracleDriver());
return DriverManager.getConnection(
"jdbc:oracle:thin:@devhost:1521:ora92", "rmenon", "rmenon");
}
private static void executePkg(Connection conn,  CallableStatement cstmt)
throws Exception {
System.out.println("Executing the package...");
cstmt.executeUpdate();
conn.commit();
}
}

Let us see what happens when we execute this program and compile the package in a different session. As before, we start the execution of this program and get the following output:

M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
\build\classes" dbj2ee.article2.design2.ExecutePackageProcedureTwice
Executing the package...
Sleeping for 20 seconds...

In another session, we recompile the package:

SQL> @pkg_body
SQL> create or replace package body pkg as
2    g_constant constant number := 1;
3    procedure p
4    is
5    begin
6      insert into t(x) values (1);
7    end p;
8  end pkg;
9  /

Package body created.

SQL> show errors;
No errors.
SQL>

And when we come back to our Java program, it outputs the following as part of the re-execution of the package:

M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
\build\classes" dbj2ee.article2.design2.ExecutePackageProcedureTwice
Executing the package...
Sleeping for 20 seconds...
Out of sleep...
Executing the package...
ORA-04068 detected - re-executing the package...
Executing the package...

As you can see we detected the error and re-executed the package successfully.

Although this solution seems to work fine, the significant drawback to it should be obvious: To implement it, we will need to catch this Exception at every place where we invoke a stored procedure in our Java code. This change would be most likely prohibitive for most systems. There is another drawback to this solution that I would mention later which even makes this solution invalid for many systems.

Our next potential solution refines the solution presented in this section to make the re-execution of the package transparent to an existing system thus making it really feasible to be implemented in .

Solution 4: Detect the ORA-0408 Error Transparently and Re-execute the Package Procedure

The idea behind this solution is as follows:

1. We substitute our own wrapper class called MyConnectionWrapper instead of the Connection implementation of Oracle. The best place is to do this substitution is at the Driver level - by writing a wrapper Driver - although you can do this substitution at the connection pooling implementation level (e.g. in the data source.)
   
2. Our Connection Wrapper would return a CallableStatement wrapper called MyCallableStatementWrapper instead of the CallableStatement implementation of Oracle whenever we invoke the method prepareCall() on it. In all other methods this wrapper class would delegate the action to the wrapped connection thus behaving in the same way as a normal Connection object.
   
3. Our CallableStatement Wrapper would trap the Exception whenever an "execute" method is invoked on it - if it detects the ORA-04068 error, it would transparently re-execute the method again on the wrapped CallableStatement object. In all other methods, it would simply delegate to the wrapped CallableStatement object.

First we will implement our own Driver that implements java.sql.Driver interface and wraps around the Oracle Driver class. The class MyDriverWrapper is shown below:

package dbj2ee.article2.design3;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.DriverPropertyInfo;
import java.sql.SQLException;
import java.util.Properties;
import oracle.jdbc.OracleDriver;
public final class MyDriverWrapper implements Driver {
private static final DriverPropertyInfo[] DRIVER_PROPERTY_INFO =
new DriverPropertyInfo[0];

public static final String ACCEPTABLE_URL_PREFIX = "jdbc:dbj2ee:orawrapper:";

private static Driver driver = new OracleDriver();

static {
try {
DriverManager.registerDriver(new MyDriverWrapper());
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection connect(String url, Properties info) throws SQLException {
String myUrl = url.replaceFirst(ACCEPTABLE_URL_PREFIX, "jdbc:oracle:thin:");
System.out.println("new url: " + myUrl);
return new MyConnectionWrapper(driver.connect(myUrl, info));
}
public DriverPropertyInfo[] getPropertyInfo(String url, Properties info)
throws SQLException {
return DRIVER_PROPERTY_INFO;
}
public boolean jdbcCompliant() {
return true;
}
public boolean acceptsURL(String url) throws SQLException {
return url != null && url.startsWith(ACCEPTABLE_URL_PREFIX);
}
public int getMinorVersion() {
return 0;
}
public int getMajorVersion() {
return 1;
}
}

Notice how the class defines its own proprietary prefix - it can be any value of your choice. It also stores an instance of the OracleDriver object which does the real work. In the connect method, the driver substitutes in the URL, its proprietary prefix with the Oracle thin driver prefix to seamlessly create a url suitable for the OracleDriver. It then gets the Oracle connection by delegating to the OracleDriver instance. It then wraps this connection with the class MyConnectionWrapper (which we would look at in a moment) and returns the MyConnectionWrapper object. This is how we substitute our own Connection object transparently. Note that you can do it in many ways - for example, you can substitute the connection at the data source level instead of doing it at the connection level.

The class MyConnectionWrapper is shown below. Observe the following facts about the implementation of this class:

1. It takes a connection object as an object in the constructor and stores it in a private instance variable.
2. It wraps all CallableStatement objects with the MyCallableStatement class in all versions of the prepareCall() method implementation.
   
3. The implementation of other methods simply delegate their action to the corresponding method in the wrapped connection.
   

package dbj2ee.article2.design3;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.Map;
public class MyConnectionWrapper implements Connection {

private Connection connection;

public MyConnectionWrapper(Connection connection) {
this.connection = connection;
}
public CallableStatement prepareCall(String sql) throws SQLException {
return new MyCallableStatementWrapper(connection.prepareCall(sql));
}
public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException {
return connection.prepareCall(sql, resultSetType, resultSetConcurrency);
}

public CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability) throws SQLException {
return connection.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
}
public void clearWarnings() throws SQLException {
connection.clearWarnings();
}
// ....... all other methods are simple delegation to the connection
//         instance variable and are not being shown to conserve space.

}

The class implementation of CallableStatement wrapper necessitates the implementation of its parent interfaces PreparedStatement and Statement. Thus we create three wrapper objects - MyStatementWrapper wraps around Statement object; MyPreparedStatementWrapper wraps around PreparedStatement object and MyCallableStatementWrapper wraps around CallableStatement object.

The class MyStatementWrapper is a simple wrapper around the Statement object and is shown partly below - the code is self-explanatory:

package dbj2ee.article2.design3;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLWarning;
import java.sql.SQLException;
import java.sql.ResultSet;

public class MyStatementWrapper implements Statement {
Statement statement;

public MyStatementWrapper(Statement statement) {
this.statement = statement;
}

public void addBatch(String sql) throws SQLException {
statement.addBatch(sql);
}

// ....... all other methods are simple delegation to the connection
//         instance variable and are not being shown to conserve space.

}

The class MyPreparedStatementWrapper is a simple wrapper around the PreparedStatement object and is shown partly below - the code is self-explanatory:

package dbj2ee.article2.design3;

import java.net.URL;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.ResultSetMetaData;
import java.sql.Array;
import java.io.InputStream;
import java.math.BigDecimal;
import java.io.Reader;
import java.sql.Date;
import java.sql.ParameterMetaData;
import java.util.Calendar;
import java.sql.Ref;
import java.sql.Time;
import java.sql.Timestamp;

public class MyPreparedStatementWrapper extends MyStatementWrapper
implements PreparedStatement {

private PreparedStatement preparedStatement;

public MyPreparedStatementWrapper(PreparedStatement preparedStatement) {
super(preparedStatement);
this.preparedStatement = preparedStatement;
}

public ParameterMetaData getParameterMetaData() throws SQLException {
return preparedStatement.getParameterMetaData();
}

// ....... all other methods are simple delegation to the connection
//instance variable and are not being shown to conserve space.
}

The class MyCallableStatementWrapper is shown below. Observe the following about the implementation of this class:

1. It extends the MyPreparedStatementWrapper class.
   
2. Like other wrappers it stores a CallableStatement object as part of its instance variable.
   
3. For all methods that execute a stored procedure, it overrides the implementation, re-invoking the method if it detects the ORA-04068 error transparently. Note that in reality you may have to override some other execute methods inherited from PreparedStatement also in a similar fashion.
   
4. The implementation of other methods simply delegate their action to the corresponding method in the wrapped CallableStatement object.

package dbj2ee.article2.design3;

import java.io.InputStream;
import java.io.Reader;
import java.util.Map;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Array;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Date;
import java.util.Calendar;
import java.sql.Ref;
import java.sql.Time;
import java.sql.Timestamp;

public class MyCallableStatementWrapper extends MyPreparedStatementWrapper
implements CallableStatement {

private CallableStatement callableStatement;
public MyCallableStatementWrapper(CallableStatement statement) {
super(statement);
this.callableStatement = (CallableStatement)statement;
}

    public boolean execute() throws SQLException {
        boolean result = true;
        try {
            result = callableStatement.execute();
        } catch (SQLException e) {
            System.out.println("code:" + e.getErrorCode() + ", sql state: "
                    + e.getSQLState());
            if(reExecutionRequired(e)){
                System.out.println("re-executing package ");
                result = callableStatement.execute();
            } else
                throw e;
        }
        return result;
    }
    
    public int executeUpdate() throws SQLException {
        int result = 0;
        try {
            result = callableStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("code:" + e.getErrorCode() + ", sql state: " +
                    e.getSQLState());
            if(reExecutionRequired(e)){
                System.out.println("re-executing package ");
                result = callableStatement.executeUpdate();
            } else
                throw e;
        }
        return result;
    }
    
    private boolean reExecutionRequired(SQLException e) {
        return "72000".equals(e.getSQLState()) && e.getErrorCode() == 4068;
    }

public URL getURL(int parameterIndex) throws SQLException {
return callableStatement.getURL(parameterIndex);
}

// ....... all other methods are simple delegation to the connection
// instance variable and are not being shown to conserve space.
}

Finally, we can take a look at our class ExecutePackageProcedureTwice that utilizes this solution. It is shown below. It is very similar to the ExecutePackageProcedureTwice in the beginning of this section - except for the following differences (shown in bold in the class listing):

1. It prints the connection and callable statement classes to show that the classes are indeed our wrapper classes.
   
2. The code for getting the connection first ensures that our driver class is loaded by using the Class.forName() trick. It then uses our proprietary prefix instead of the "oracle:jdbc:thin:" prefix so that our driver is selected by the DriverManager when obtaining the connection thus making it possible to substitute all the relevant JDBC classes with our wrapper classes.
   

package dbj2ee.article2.design3;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class ExecutePackageProcedureTwice {
public  static void main(String[] args) throws Exception {
Connection conn = null;
CallableStatement cstmt = null;
long sleepInSecs = 20;
try {
conn = getConnection();
System.out.println("connection class: " + conn.getClass());
cstmt = conn.prepareCall("{call pkg.p()}");
executePkg(conn, cstmt);
System.out.println("Sleeping for " + sleepInSecs + " seconds...");
Thread.sleep(sleepInSecs*1000);
System.out.println("Out of sleep...");
executePkg(conn, cstmt);
} finally {
try {
if(cstmt != null)
  cstmt.close();
if(conn != null)
  conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
private static Connection getConnection() throws Exception {
Class.forName("dbj2ee.article2.design3.MyDriverWrapper");
       return DriverManager.getConnection(MyDriverWrapper.ACCEPTABLE_URL_PREFIX +
               "rmenon/rmenon@devhost:1521:ora92");
}
private static void executePkg(Connection conn,  CallableStatement cstmt)
throws Exception {
System.out.println("Executing the package...");
cstmt.executeUpdate();
conn.commit();
}
}

When we execute this class, we get the following results (notice the connection class and the callable statement classes point to our wrapper classes):

M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
\build\classes" dbj2ee.article2.design3.ExecutePackageProcedureTwice
new url: jdbc:oracle:thin:rmenon/rmenon@devhost:1521:ora92
connection class: class dbj2ee.article2.design3.MyConnectionWrapper
callable statement class: class dbj2ee.article2.design3.MyCallableStatementWrapper
Executing the package...
Sleeping for 20 seconds...

We then recompile the package in another session as before:

SQL> @pkg_body
SQL> create or replace package body pkg as
2    g_constant constant number := 1;
3    procedure p
4    is
5    begin
6      insert into t(x) values (1);
7    end p;
8  end pkg;
9  /

Package body created.

SQL> show errors;
No errors.

And when we observe back in our Java execution, we see the following after the Program successfully re-executes the package:

M:\articles\dbj2ee\articles>java -cp "M:\classes12.jar;.;M:\learning\java\dbj2ee
\build\classes" dbj2ee.article2.design3.ExecutePackageProcedureTwice
new url: jdbc:oracle:thin:rmenon/rmenon@devhost:1521:ora92
connection class: class dbj2ee.article2.design3.MyConnectionWrapper
callable statement class: class dbj2ee.article2.design3.MyCallableStatementWrapper
Executing the package...
Sleeping for 20 seconds...
Out of sleep...
Executing the package...
code:4068, sql state: 72000
re-executing package

Note that if you use a connection pool then you can specify the correct driver in the connection pool to use the same technique.

Thus we are able to device a solution that seems to work transparently for almost all cases.
This is what I thought would be the perfect solution before I had some discussions with Tom Kyte on his web site http://asktom.oracle.com. He pointed out a caveat in this seemingly perfect solution which I now describe.

Consider the following scenario:

1. You have a package pkg which depends on a constant const1 in the package const. The package pkg has two methods method1 and method2 that both rely on the constant const1 - the value is set to 1.
   
2. You grab a connection from the connection pool.
3. In your Java code, you execute the method pkg.method1 - it makes some decision on the constant value which is right now 1.
4. Now, as part of the deployment, someone compiles the package const - the constant has been changed to have a value of 2.
5. Your transaction executes next step which is invoking the method pkg.method2.
6. Since you have implemented the "silent re-execution technique" mentioned in this section, the method2 silently ignores the ORA-04068 and grabs the new value of the constant which is 2.
7. The problem is that this may result in inconsistent results in the transaction. This is because you voilated the assumption that a constant declared in a package (or package state, in general) should give the same value throughout a given session - otherwise there is no guarantee that you would get a consistent result depending on the transaction semantics.
   

Thus this solution is not applicable in all cases where the re-execution of the package procedure does not give the correct results. This can happen, if for example, your package procedure's current execution depends on the previous package state. This was the scenario that Tom elaborated on and pointed out that it may be more common than I had thought earlier.

Conclusion and Recommended Solution(s)

We looked at multuple solutions in this article for the ORA-04068 error and also gave various trade-offs for each solution. Following is what I recommend depending on individual scenario:

1. In all cases I recommend not using any global variables in the package specification or body as far as possible.
   
2. The easiest solution is to use stateless packages (our solution 1) and if you can go with this then that is what I recommend. You should strive to make your packages stateless.
   
3. The second best solution (which would be applicable in majority of cases), is to add companion packages for each package where the package state is isolated. This ensures that you encounter ORA-04068 only when you actually change the companion state packages - which should be relatively rare - especially if the state consists of only constants. If you don't like to have companion packages, then you can have a central package for all constants in the system - this would result in more ORA-04048 than usual - but note that you typically need to flush your connection pool even if you change the state of one package so this is not as bad as it sounds.
   
4. I do not recommend solution 4 (or solution 3) since I find it hard to guarantee that they would work in any complicated system. However rare the number of times they would result in a failure, this solution would act like a ticking bomb ready to explode in those rare situations.
   

Acknowledgements

I would like to acknowledge my colleagues Ivan Zlatev and Andrey Radchenko, discussions with whom led to the solution 4 outlined in this article. I would also acknowledge Tom Kyte, discussions with whom led to my not recommending solution 4 after all.