Home » SQL & PL/SQL » SQL & PL/SQL » existing state of package - Invalidated (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
existing state of package - Invalidated [message #667196] Thu, 14 December 2017 04:11 Go to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

I am executing one package from Linux shell script.This will invoke JAVA application, from that Database procedure will gets executed.

But when I am running continuously the same job, some time following error is coming ,but
some time it executing successfully.

When I am running the same procedure complete from the DATABASE, always its executing successfully.

Unable to find out under which scenarios,existing STATE OF PACKAGE is becoming INVALIDATE


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

Please help me to understand on this .

Thanks
SaiPradyumn



Re: existing state of package - Invalidated [message #667200 is a reply to message #667196] Thu, 14 December 2017 07:08 Go to previous messageGo to next message
BlackSwan
Messages: 26648
Registered: January 2009
Location: SoCal
Senior Member
Does application issue dynamic DDL?
Re: existing state of package - Invalidated [message #667201 is a reply to message #667200] Thu, 14 December 2017 07:53 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member

Yes, BlackSwan

My package uses Execute immediate to perform some DDL operations.But I had checked the status of that Object
from user_objects. Every time both spec, body are in VALID status only.

Thanks
SaiPradyumn
Re: existing state of package - Invalidated [message #667202 is a reply to message #667201] Thu, 14 December 2017 07:59 Go to previous messageGo to next message
BlackSwan
Messages: 26648
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Thu, 14 December 2017 05:53

Yes, BlackSwan

My package uses Execute immediate to perform some DDL operations.But I had checked the status of that Object
from user_objects. Every time both spec, body are in VALID status only.

Thanks
SaiPradyumn
Packages will be marked as INVALIDATE when any object they reference is impacted by DDL.

IMO, any application that does dynamic DDL is a flawed design.
Application objects should be known & fixed between application version releases.

You now observe bug manifestation from flawed design & implementation.
Re: existing state of package - Invalidated [message #667204 is a reply to message #667201] Thu, 14 December 2017 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 66773
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-04061: existing state of %s has been invalidated
 *Cause:  Attempt to resume the execution of a stored procedure using the
          existing state which has become invalid or inconsistent
          with the stored procedure because the procedure has been altered
          or dropped.
 *Action: Try again; this error should have caused the existing state of
          all packages to be re-initialized.
The package may be valid but its state inside the current sessions is no more valid as from a previous "instance" of the package.

Re: existing state of package - Invalidated [message #667209 is a reply to message #667204] Thu, 14 December 2017 23:30 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Blakcswan, Michel

Thanks for your valuable inputs.

My issue has been resolved with the following steps :

1: Killed all the sessions with are associated with my package & dependent objects .

select DISTINCT SESSION_ID
from SYS.DBA_DDL_LOCKS   where NAME  like  '%My_PACK%'

2:Executed the following package from the SQL Developer.

BEGIN
sys.dbms_session.reset_package;
END;

3:Complied the Spec&Body , dependent objects once again after executing the reset package .

After the above 3 steps my job was executing successfully from Linux also

Thanks once again Smile

Thanks
SaiPradyumn
Re: existing state of package - Invalidated [message #667212 is a reply to message #667209] Fri, 15 December 2017 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 66773
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Re: existing state of package - Invalidated [message #675291 is a reply to message #667209] Thu, 21 March 2019 07:09 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel,

When we got the following error, we are resolving the issue by killing the associated sessions, Reset Package block, compilation of
dependent objects


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


But we are unable to find out why it is coming frequently. We are not giving any alternations/compilations
to the dependent objects .

Need to understand which circumstances lead to discard the existing state?
If the current session is discarding the existing state, Is there any way to find out the state of the object
(with the help of any data dictionary views )?

Thanks
SaiPradyumn
Re: existing state of package - Invalidated [message #675292 is a reply to message #675291] Thu, 21 March 2019 07:57 Go to previous messageGo to next message
BlackSwan
Messages: 26648
Registered: January 2009
Location: SoCal
Senior Member
> We are not giving any alternations/compilations to the dependent objects .
Does application issue any DDL (CREATE, DROP, ALTER, etc.) as part of normal processing?
Re: existing state of package - Invalidated [message #675293 is a reply to message #675291] Thu, 21 March 2019 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
It only happens if the package is recompiled directly or if one of it's dependant objects is altered.
It also only happens if the package in question has global variables (A package without globals has no concept of state).

Re: existing state of package - Invalidated [message #675294 is a reply to message #675293] Thu, 21 March 2019 08:22 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member


We are calling one normal procedure which is calling one more package procedure.We have some DDL in side the normal procedure.
package also have some global variables.

But here the issue is this problem is not happening every time.
As you said, i tried by altering the dependent objects in UAT.
But its not replicating

Thanks
SaiPradyumn
Re: existing state of package - Invalidated [message #675295 is a reply to message #675294] Thu, 21 March 2019 08:33 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
That doesn't really help us. If you show us the code we may be able to spot the problem, but without that there's nothing we can really add.
What DDL are you running?
Re: existing state of package - Invalidated [message #675296 is a reply to message #675291] Thu, 21 March 2019 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 66773
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This only happens to a session that used a package containing global variables and this package is later recompiled (directly or automatically due to an alteration of it or one of the objects it depends on) and the former session tries to then reuse the package.

Re: existing state of package - Invalidated [message #675325 is a reply to message #675296] Fri, 22 March 2019 03:53 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

Thanks for your explanation.These are some alternation to the some of the dependent objects which is leading to the ORA-04061 Error

Following are few more observation on this Error

We are getting this error when we apply the database release on mid of the week.
If we are applying the releases on the week end(Saturday),we never faced this issue.
Application server will be shut down in week ends, but database server will be always UP.
That particular job will be triggered at end of the day during the week days(Mon to Fri)

If the execute that procedure completely from the back end(SQL Developer tool)
we never faced any issue with the state of the object.
But in PROD/UAT this will be triggered through Linux box


Before raising the exception it self , is there any way to find out state of the Object.


Thanks for your support to understand scenario.

Thanks
SaiPradyumn


Re: existing state of package - Invalidated [message #675329 is a reply to message #675325] Fri, 22 March 2019 04:24 Go to previous messageGo to next message
cookiemonster
Messages: 13777
Registered: September 2008
Location: Rainy Manchester
Senior Member
So your doing DB changes that invalidate a package that has a state while the application is connected?
This is going to be hard to avoid.
Why can't you just temporarily shutdown the application?
Do you really need the global variables (this problem will go away if they're removed)?
Re: existing state of package - Invalidated [message #675332 is a reply to message #675329] Fri, 22 March 2019 04:52 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member

Yes, during the week days the application was connected but in the week end all servers will be shout down.
Mostly we will apply the db releases on the week ends only.

But as you suggested will remove the global variables in the package to avoid the error in the feature

Thanks for your explanation
SaiPradyumn
Re: existing state of package - Invalidated [message #675357 is a reply to message #675332] Fri, 22 March 2019 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 66773
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But as you suggested will remove the global variables in the package to avoid the error in the feature
No, the correct way to NOT upgrade your application during work hours and, in your case, only week-ends.
An alternative is to use EDITIONs.

Re: existing state of package - Invalidated [message #675378 is a reply to message #675357] Mon, 25 March 2019 07:49 Go to previous message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel,

Thanks for your valuable suggestions.We will completely avoid the DB Deployments in production on Week days.

But in UAT, if there is necessity to apply the releases we will shut down all application servers to avoid this kind of issues .

Thanks
SaiPradyumn

Previous Topic: TO_NUMBER specify the decimal separator within a query.
Next Topic: Duplicate Insertion while executing Procedure
Goto Forum:
  


Current Time: Fri Jan 17 21:21:23 CST 2020