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: 318
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: 25793
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: 318
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: 25793
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: 65317
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: 318
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 message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Previous Topic: Need to Retrieve data from JSON file in Oracle sql select query
Next Topic: CREATE VIEW with "SELECT * FROM" several tables
Goto Forum:
  


Current Time: Wed Jan 17 15:11:58 CST 2018

Total time taken to generate the page: 0.01477 seconds