Oracle, how to manually re-instantiate a PL/SQL package

errorsoracleoracle-11gplsqlreplace

I have a web application which calls a procedure from an Oracle PL/SQL package. Each time the package is replaced in turn of a new release the application runs into an exception ORA-06508

java.sql.SQLException: ORA-04068: Der bestehende Status der Packages  wurde  aufgehoben
ORA-04061: Bestehender Status von package body "OPS$XY.PACK_XY" wurde annulliert
ORA-04065: Ausführung nicht erfolgreich, package body "OPS$XY.PACK_XY" wurde geändert oder gelöscht
ORA-06508: PL/SQL: aufgerufene Programmeinheit : "OPS$XY.PACK_XY" konnte nicht gefunden werden
ORA-06512: in Zeile 1

From the Oracle-Docs I got the info that the package needs to be re-instantiated, which is done by the next PL/SQL call after the ORA-06508– error.

Is there a way to re-instantiate this package manually right after CREATE OR REPLACE PACKAGE ?

Best Answer

The answer is NO.

Now you have two options IMHO.

  1. You can make the package stateless by removing package variables.
  2. You can catch this ORA-ORA-04061 in the application code and re-execute the command.

Useful links:

  • AskTom
  • ORA-04061: existing state of has been invalidated