Menu
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)
586
Packages become invalidated when any database object (table, view etc.) that that package has dependecies on is altered in any way. When a Package is being used it is loaded into memory and a "session state" is created for that package.
A package may become invalid for various reasons such as in the following cases: Table Invalid: One or more objects referred by the procedure or package has been altered the procedure wil be marked as invalid. For example, this can happen when one column has been added in a table referred to inside the package.
SQL> SQL> select object_type, status 2 from user_objects 3 where object_name = 'PKG'; OBJECT_TYPE STATUS ----------------------- ------- PACKAGE VALID PACKAGE BODY VALID 2 rows selected. You can see that DDL on EMP (which PKG depends on) made it invalid.
Oracle recommends that you run the utlrp.sql script after you install, patch, or upgrade a database, to identify and recompile invalid objects. The utlrp.sql script recompiles all invalid objects. Run the script immediately after installation, to ensure that users do not encounter invalid objects.
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
DETERMINISTIC functions (as suggested by this answer)PRAGMA SERIALLY_REUSABLE causes Oracle to re-initialize the global variables with every call to the server.
86
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
