PL/SQL Package invalidated

oracle plsql
FrustratedWithFormsDesigner picture FrustratedWithFormsDesigner · Mar 23, 2010 · Viewed 29.4k times · Source

I have a script that makes use of a package (PKG_MY_PACKAGE). I will change some of the fields in a query in that package and then recompile it (I don't change or compile any other packages). I run the script and I get an error that looks like 

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

I run the script again (without changing anything else in the system) and the script executes successfully.

I thought that when I compiled before I executed the script that would fix any invalid references. This is 100% reproducible, and the more I use this script the more annoying it gets. What could cause this, and what would fix it?

(oracle 10g, using PL/SQL Developer 7)

Answer

Peter Lang picture Peter Lang · Mar 23, 2010

Background

existing state of packages has been discarded means, that your Package had some sort of state.

This is caused by a global variable stored in your Package Body.
Until 11.2.0.2, constants did also cause this behavior (see documentation).

Since the package has already been used in your session, Oracle assumes that this state is relevant for you. Some of these variables might have different values now, and when you recompile the Body, the values are reset.

This exception is thrown, so that your clients know that they can't rely on those variables any more.

Solutions

  • Remove all global variables and constants (before 11gR2) from the Package Body if possible
  • Replace global variables by DETERMINISTIC functions (as suggested by this answer)
  • Defining packages with PRAGMA SERIALLY_REUSABLE causes Oracle to re-initialize the global variables with every call to the server.
  • Close your session and reconnect before calling the package again.
  • Reset the state manually (see Paul James' answer)

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More