Ora-4068 Handler

What is an ORA-4068 Error?

An ORA-4068 error occurs when:

• you have a pl/sql package
• someone uses the package
• the package maintains a state
• you recreate the package (deploy a new version), killing their state

How is an ORA-4068 Error Handled?

The first time a pl/sql package is invoked it will throw an ORA-4068 error.  One simply needs to capture this error and retry the call to the pl/sql package.  This will 'clean' a flag on the DB connection and the ORA-4068 error should no longer be thrown.  When using a DB connection pool it is possible to migrate connections between retries and receive a connection that has not had the ORA-4068 'flag' cleared, thus requiring a max of NUM_CONNECTIONS_IN_DB_POOL + 1 retries.

Why should I care about handling ORA-4068 Errors?

By handling ORA-4068 errors you allow the database team to deploy new pl/sql package versions as much as they like without requiring a restart of your application (yay)!

Code Example:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleDriver;
import java.sql.SQLException;
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 lib_history_pkg.log_book(?,?)}");
    cstmt.setString(1,"1");
    cstmt.setString(2,"1");
    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:@test_db01.abc.com:1521:test1", "app_user", "abc");
}
private static void executePkg(Connection conn,  CallableStatement cstmt) throws Exception {
System.out.println("Executing the package...");
cstmt.executeUpdate();
conn.commit();
}
}

Reference:

http://yong321.freeshell.org/oranotes/4068.txt

.