I suspect you’re only reporting the last error in a stack like this:
If so, that’s because your package is stateful:
The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.
When you recompile the state is lost:
If the body of an instantiated, stateful package is recompiled (either explicitly, with the “ALTER PACKAGE Statement”, or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.
After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it…
You can’t avoid this if your package has state. I think it’s fairly rare to really need a package to be stateful though, so you should revisit anything you have declared in the package, but outside a function or procedure, to see if it’s really needed at that level. Since you’re on 10g though, that includes constants, not just variables and cursors.
But the last paragraph from the quoted documentation means that the next time you reference the package in the same session, you won’t get the error and it will work as normal (until you recompile again).
Oracle Database 11g Release 2 (220.127.116.11), Oracle Database treats a package as stateless if its state is constant for the life of a session (or longer). This is the case for a package whose items are all compile-time constants.
A compile-time constant is a constant whose value the PL/SQL compiler can determine at compilation time. A constant whose initial value is a literal is always a compile-time constant. A constant whose initial value is not a literal, but which the optimizer reduces to a literal, is also a compile-time constant. Whether the
PL/SQLoptimizer can reduce a nonliteral expression to a literal depends on optimization level. Therefore, a package that is stateless when compiled at one optimization level might be stateful when compiled at a different optimization level. For information about the optimizer, see “PL/SQL Optimizer”.
Each session that references a package item has its own instantiation of that package. If the package is stateful, the instantiation includes its state. The package state persists for the life of a session, except in these situations:
- The package is
SERIALLY_REUSABLE.For details, see “SERIALLY_REUSABLE Packages”.
- The package body is recompiled.If the body of an instantiated, stateful package is recompiled (either explicitly, with the “ALTER PACKAGE Statement”, or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception
ORA-04068.After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it. Therefore, previous changes to the package state are lost. (For information about initialization, see “Package Instantiation and Initialization”.)
- Any of the session’s instantiated packages are invalidated and revalidated.All of a session’s package instantiations (including package states) can be lost if any of the session’s instantiated packages are invalidated and revalidated. For information about invalidation and revalidation of schema objects, see Oracle Database Advanced Application Developer’s Guide.
It sounds like what you want is to be able to list all packages that may potentially have state.
What you’re looking for is just packages that have any global variables or constants. For a single package, this is quite simple by inspection. To look across all packages in a schema, however, you could use PL/Scope:
First, log in as the schema owner, turn on PL/Scope in your session:
Then, recompile all your package bodies.
Then, run this query to find all the variables and constants declared at the package level:
I’d suggest the resulting list of packages will be your target.
If you’re on
11gR2, constants no longer cause this problem, so you’d use this query instead:
And if you want to know how to prevent discarded package states….
Move all constants and variables into a stand-alone package spec and reference those from your initial package. Thus when the status of your original package is invalidated for whatever reason, it has no package state and can be recompiled automatically, however the package containing the vars/const will not become invalidated as it has no dependencies, so the state that is in memory for that package will remain and can continue to be used.
As for having package level cursors, you’ll need to make these local to the procedures/functions using them as you won’t be able to reference cursors across packages like that (not sure about using REF CURSORS though…. there’s one for me to investigate!)
This first example shows the package state being invalided by the addition of a new column on the table, and causing it to give a “Package state discarded” error…
And this next example shows how having the package variables in their own package spec, allows the package to automatically recompile when it is called even though it became invalidated by the action of adding a column to the table.