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.
