Home » SQL & PL/SQL » SQL & PL/SQL » status invalid for stored procedures
status invalid for stored procedures [message #36274] Wed, 14 November 2001 06:42 Go to next message
ash
Messages: 43
Registered: February 2001
Member
Hi All,

Whenever I connect to the Oracle database through SQL Navigator and open a stored procedure, the status of all other SP's becomes invalid. So every time before I logout I recompile all the SP's.

These SP's I am using to return a cursor as an open recordset to Crystal reports. Other SP's which are not used for reports have no problem.

Can anybody suggest me what is the problem ?

Thanks for any help!

----------------------------------------------------------------------
Re: status invalid for stored procedures [message #36291 is a reply to message #36274] Wed, 14 November 2001 10:38 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
If there is any DML on the underlying tables (and other objects) you would encounter this. Query something like this to see if that's the case.
SELECT object_name, object_type, created, last_ddl_time
FROM user_objects
WHERE created > SYSDATE - 1
OR last_ddl_time > SYSDATE - 1;

----------------------------------------------------------------------
Re: status invalid for stored procedures [message #36294 is a reply to message #36274] Wed, 14 November 2001 10:59 Go to previous message
ash
Messages: 43
Registered: February 2001
Member
I am getting all these SP's since I compiled them today.
However, for reference to objects, I am using one ref cursor in all sp's which is kept open as I am returning it as a recordset. Will this be the reason for Invalid status ?

----------------------------------------------------------------------
Previous Topic: Something weird is happening...
Next Topic: Help Convert VARCHAR2 to number
Goto Forum:
  


Current Time: Fri Mar 29 00:42:55 CDT 2024