2 Replies Latest reply on Mar 22, 2016 10:48 AM by Sravprem

    ORA-04061: existing state of  has been invalidated

    Sravprem

      I have a package (AAA) which having three procedures (let assume procedure name like one_pro, two_pro, and three_pro).  These three package procedures are calling through database triggers (After insert or UPDATE) which are at po_header Level , line level and shipment level.

      Header Level trigger calls  AAA.one_pro,

      Line Level trigger calls  AAA.two_pro

      Shipment Level trigger calls  AAA.three_pro

       

      I have one more procedure(BBB) which was scheduled for every 5mins, and updates po_line_location_all table record. If record meets business validations.

       

      Recently I have done small changes in cursor query of AAA.one_pro and AAA.two_pro and migrated AAA package body into productions. From that day onwards program (BBB) is getting terminated by throwing below error message

       

      ORA-04061: existing state of  has been invalidated ORA-04061: existing state of package body "APPS. AAA " has been invalidated

      ORA-04065: not executed, altered or dropped package body "APPS. AAA"

      ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at "APPS.XXXXXXXXXX(shipment Level trigger name", line 53

      ORA-04088: error during execution of trigger 'APPS.XXXXXXXXXX’(shipment Level trigger name)

       

      I have verified the status of AAA package and its dependent objects.  All are in valid status.

       

      Can anyone help me on this What may be the reason for this error?  AAA  package body contain global variables .My DBA migrated this object in business hours when users are using the application.

        • 1. Re: ORA-04061: existing state of  has been invalidated
          PamNL

          See if this solves your issue

          compilation - revalidation - confusion

           

          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.

          • 2. Re: ORA-04061: existing state of  has been invalidated
            Sravprem

            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