CODEWITHSUNDEEP
javaoracle-databaseplsql
Ravindra babu
Ravindra babu

Reputation: 38910

Effective handling java.sql.SQLException: ORA-04068

I got below Exception: 

Caused by: java.sql.SQLException: ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "PACKAGE_A" has been invalidated
ORA-04065: not executed, altered or dropped package body "PACKAGE_A"
ORA-06508: PL/SQL: could not find program unit being called: "PACKAGE_A"

Versions:

Java 1.7.x and oracle 11.4.x

Root cause: PACKAGE_A has state with global variable declarations. The package specification ( not body) has been recompiled & java services calling PACKAGE_B which internally calls PACKAGE_A have thrown this exception until we restart these services.

I know that state should not be present package specification. But I am looking for feasible solution without code changes in JAVA

I have found two solution from other SE questions regarding this topic.

Option 1:

Defining packages with `PRAGMA SERIALLY_REUSABLE`

Option 2:

exec DBMS_SESSION.RESET_PACKAGE
exec DBMS_SESSION.MODIFY_PACKAGE_STATE( DBMS_SESSION.REINITIALIZE)

Option 3:

Removing global variables from package.

If I did not go for Option 3 forever, what are the disadvantages of using Option 1 & Option 2?

Does these options cause performance overhead on database? Does option 3 provides best solution?

Upvotes: 3

Views: 2416

Answers (1)

Gee Bee
Gee Bee

Reputation: 1794

This is the standard behavior of Oracle, since it keeps a compiled instance of the packages in the session memory of each session. Because of all package states are valid within a session, (i.e. a package variable can have different value in two database sessions) also the package variable values are bound to a session.

Now if you change a package, Oracle has to re-load the package for all the sessions. So far so good. However as you have a code change, the package variables are getting lost. This is like stopping and restaring an application after a code change - the variables are getting lost.

This graceful exception is showing that now the package variables are getting lost.

Depending on how you're using the package variables, this can cause problems in your application - hence the exception.

But in the real life, all package variables are used so that they're initialized on demand (e.g. oldschool caching with plsql tables), and no one shall keep persistent data (for example, content of the shopping cart in a webshop app) in package variables. In fact, package variables - although look persistent - shall be treated as transient with an optimistic persistency between calls.

If you can keep this rule, then you can handle this exception very easily:

  • do not change package code :) ok, just kidding
  • simply re-run the failed pl/sql call when getting this exception.

I.e.:

  • call addCustomer(?,?,?)
  • you got a java.sql.SQLException: ORA-04068
  • now call again the same addCustomer(?,?,?)

Upvotes: 1

Related Questions