existing state has been invalidated.. best way to trouble shoot?

[kishj]

Greetings.

One of my users is getting this (I have replaced the actual package with the string 'NAMEOFPACKAGE', and the name of the database with 'THEDB'):

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

Can someone recommend the best way for me to trouble shoot this and figure out what the cause of the problem is? They said they tried to run the application that is generating this error several times.
The 'NAMEOFPACKAGE' is one we have written, and seems to work fine in other databases.


Thanks
Jeff Kish

[pwoneill]

We've seen this problems many times with Oracle Workflow. I don't really know what the cause of the problem is, but it seemed to happen whenever we made a modification to the code (even though everything was compiled correctly). It might have happened on it's own or after some other modification (i wasn't really in charge of workflow).

We were able to work around it by reloading then recompiling all of the invalid objects in whatever package is in error, in our case WFCORE and WFENG. To recompile we just wrote a small script that generated a list of all invalid objects in the database and compiled them, a la

<font face="courier">
SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID' AND OBJECT_TYPE <> 'PACKAGE BODY'
/
SELECT 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;'
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID' AND OBJECT_TYPE = 'PACKAGE BODY'
/
</font>

so reload whatever packages are complaining, then spool that output to another file, then run it and see if that fixes your problem.

if anybody knows the real reason for this, or how to prevent it, i'd love to hear it.

[kishj]

I will keep you posted. This seems to happen on only one database instance that I know of.
Perhaps it is something to do with how the Oracle was installed. I will let you
know if I find out anything interesting.

Thanks
Jeff

[srinigenie]

The cause for this is the SGA (memory that is allocated by an Oracle Instance and is shared among Oracle processes). Any object in SGA referencing an object that is compiled become invalid during this compilation.

So in this case the process accessing the compiled object needs to be restarted. For example, in case of Oracle Workflows, this error can be rectified by restarting the Workflow Mailer ( or a Retry also helps). One more way is to bounce the database itself which ofcourse will be a last resort.

[davey23uk]

over 6 years old, I think the problem has been sovled already

[qwertyuiop]

Maybe 6 years old but I'm sure there are plenty of peeps who still will find it useful.

I think maybe you are regenerating your package with a script like

create of replace package rgregeg
as
......
....
end wefwefw
/

If so, make sure you issue a

alter package regerger compile

command afterwards. This should enable the workflow to work correctly without rebouncing anything.