"ORA-04068: existing state of packages has been discarded ORA-04065: not executed, altered or dropped stored procedure

Question

i m getting below error when i m trying to execute procedure "PROCEDURE_NAME" under procedure "CALLING_PROCEDURE_NAME". But my PROCEDURE_NAME is already in valid state and successfully using in some other procedures.

ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure ""PROCEDURE_NAME""
ORA-06508: PL/SQL: could not find program unit being called: ""PROCEDURE_NAME""
ORA-06512: at ""CALLING_PROCEDURE_NAME"", line LINE_NO
ORA-06512: at line 1

Answer 1

But my PROCEDURE_NAME is already in valid state and successfully using in some other procedures.

The session where the package is currently called, it retains that state of the package. If you recompile the package, then the moment the package is called in that session again, you will hit this error.

1. You can execute DBMS_SESSION.RESET_PACKAGE; to free the memory, cursors, and package variables after the PL/SQL call that made the invocation finishes running.

2. You could close all existing sessions and re-execute.

3. You could make the package, SERIALLY_REUSABLE Packages by using PRAGMA SERIALLY_REUSABLE; statement. If a package is SERIALLY_REUSABLE, its package state is stored in a work area in a small pool in the system global area (SGA). The package state persists only for the life of a server call.