See if this solves your issue
A package becomes invalid, and would remain invalid even if you try to recompile it. (I don't think this is your situation.)
A package becomes invalid, but can be recompiled. A trigger trying to execute this package can cause it to be implicitly compiled. (This is the situation you want to be in.)
A package becomes invalid, and it can be recompiled, but it uses global variables which cause the package to retain a 'state'. (This is the situation you are in, from which you hopefully can escape.)
Unlike a simple procedure, a package can maintain a state with global variables, in either its spec or its body. This package can retain a state:
create or replace package pkg_a as this_is_a_global_var number; procedure proc1; procedure proc2; end pkg_a; /
This package can not:
create or replace package pkg_b as procedure proc1; procedure proc2; end pkg_b; /
Because this_is_a_global_var is not local to any procedure, it will persist from the time pkg_a is initialized in the session until the session ends (unless you recompile and invalidate the state; I believe there are other ways to clear the state as well). The same situation applies to variables declared globally in the package body (i.e. outside of all procedures/functions).
For example, if you call pkg_a.proc1 then it can set this_is_a_global_var. (It does not matter whether it really does or not; the issue is that it could.) A later reference to pkg_a by the same session should find this_is_a_global_var with the value that was set earlier in the session. By initializing the package, you have created a state, which Oracle will maintain.
If you recompile the package (explicitly or implicitly, by attempting execution) then the state will be lost. If you then try to invoke the package in the same session, you will get ORA-04601. But this will happen only once - once the state is discarded, a new execution starts with a 'clean slate' and the state is re-initialized. That is why it works the second time.
To solve your problem, remove all global variables from the troublesome package (from both spec and body). You now have a stateless package and you will not get If you really need to maintain a state, you can move them to another package that will not be invalidated/recompiled by table changes.
Hi, Thanks for reply.
As you suggested I have removed all global variables in specification and body header and maintained those variable in individual procedures of the package. But still I'm facing the same issue.
When other procedure (BBB) trying to update the po_header_all or po_lines_all table then it is throwing the error
ORA-04061: existing state of has been invalidated
ORA-04061: existing state of package ""APPS. AAA" has been invalidated
ORA-04065: not executed, altered or dropped package ""APPS. AAA"
ORA-06508: PL/SQL: could not find program unit being called
Do I need to follow any steps like need to log out from application or any other formalities? if I recompile the package spec and boby without global variable will state will be lost?
Interestingly, When I update the record from front end(PO screen) it is not giving any error. Data base trigger is firing and calling AAA procedure with out any issue. Only this issue is coming when i update the PO tables through program