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

As of Oracle Database 11<span class="italic">g</span> Release 2 (11.2.0.2), 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/SQL optimizer 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:

alter session set plscope_settings='IDENTIFIERS:ALL';

Then, recompile all your package bodies.

Then, run this query to find all the variables and constants declared at the package level:

select object_name AS package, type, name AS variable_name from user_identifiers
where object_type IN ('PACKAGE','PACKAGE BODY') and usage = 'DECLARATION' and type in ('VARIABLE','CONSTANT') and usage_context_id in ( select usage_id from user_identifiers where type = 'PACKAGE' );

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:

Example code

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…

SQL> set serveroutput on
SQL>
SQL> create table dependonme (x number)
  2  / 
 
Table created.
 
SQL>
SQL> insert into dependonme values (5)
  2  / 
 
1 row created.
 
SQL>
SQL> create or replace package mypkg is
  2    procedure myproc;
  3  end mypkg;
  4  / 
 
Package created.
 
SQL>
SQL> create or replace package body mypkg is
  2    v_statevar number := 5; -- this means my package has a state
  3
  4    procedure myproc is
  5      myval number;
  6    begin
  7      select x
  8      into myval
  9      from dependonme;
 10
 11      myval := myval * v_statevar;
 12      DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 13    end;
 14  end mypkg;
 15  / 
 
Package body created.
 
SQL>
SQL> exec mypkg.myproc
My Result is: 25
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
  2  / 
 
OBJECT_NAME
--------------------------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
MYPKG
PACKAGE             VALID
 
MYPKG
PACKAGE BODY        VALID
 
 
SQL>
SQL>
SQL> alter table dependonme add (y number)
  2  / 
 
Table altered.
 
SQL>
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
  2  / 
 
OBJECT_NAME
--------------------------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
MYPKG
PACKAGE             VALID
 
MYPKG
PACKAGE BODY        INVALID
 
 
SQL>
SQL> exec mypkg.myproc
BEGIN mypkg.myproc; END;
 
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SCOTT.MYPKG" has been invalidated
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1
 
 
SQL>
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
  2  / 
 
OBJECT_NAME
--------------------------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
MYPKG
PACKAGE             VALID
 
MYPKG
PACKAGE BODY        INVALID
 
 
SQL>
SQL> exec mypkg.myproc
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
  2  / 
 
OBJECT_NAME
--------------------------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
MYPKG
PACKAGE             VALID
 
MYPKG
PACKAGE BODY        VALID

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.

SQL> drop table dependonme
  2  / 
 
Table dropped.
 
SQL>
SQL> drop package mypkg
  2  / 
 
Package dropped.
 
SQL>
SQL> set serveroutput on
SQL>
SQL> create table dependonme (x number)
  2  / 
 
Table created.
 
SQL>
SQL> insert into dependonme values (5)
  2  / 
 
1 row created.
 
SQL>
SQL> create or replace package mypkg is
  2    procedure myproc;
  3  end mypkg;
  4  / 
 
Package created.
 
SQL>
SQL> create or replace package mypkg_state is
  2    v_statevar number := 5; -- package state in seperate package spec
  3  end mypkg_state;
  4  / 
 
Package created.
 
SQL>
SQL> create or replace package body mypkg is
  2    -- this package has no state area
  3
  4    procedure myproc is
  5      myval number;
  6    begin
  7      select x
  8      into myval
  9      from dependonme;
 10
 11      myval := myval * mypkg_state.v_statevar;  -- note: references the mypkg_state package
 12      DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 13    end;
 14  end mypkg;
 15  / 
 
Package body created.
 
SQL>
SQL> exec mypkg.myproc
My Result is: 25
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
  2  / 
 
OBJECT_NAME
--------------------------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
MYPKG
PACKAGE             VALID
 
MYPKG
PACKAGE BODY        VALID
 
 
SQL>
SQL> alter table dependonme add (y number)
  2  / 
 
Table altered.
 
SQL>
SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
  2  / 
 
OBJECT_NAME
--------------------------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
MYPKG
PACKAGE             VALID
 
MYPKG
PACKAGE BODY        INVALID

PL/SQL procedure successfully completed.

Source

1. Annals of Oracle’s Improbable Errors: ORA-04061: existing state of package has been invalidated.
2. https://stackoverflow.com/questions/19376440/ora-06508-pl-sql-could-not-find-program-unit-being-called/19380746
3. https://stackoverflow.com/questions/23553257/asp-net-ora-04068-existing-state-of-packages-has-been-discarded/23553334#23553334