Procedure invalid state

[spicysudhi]

Hi

i am not sure whether this thread should goto vb or in oracle....

this is something strange i found in 9i. we are calling stored procedures from VB application. When the DB was in 8i, if a procedure state is invalid, the call from VB used to recompile the procedure and execute it. but after upgrade to 9i, if the procedure state is invalid, it just throws the error in vb application.

if i call procedure directly from sqlplus it works.
procedure invalid state does not mean there is error in procedure, it could be invalid because the underlying table structure change or something.

can someone guide why is this difference in 9i, or is there a parameter that sets the automatic compilation.

thanks in advance,
regard,s
sudhi

 

[vivekm]

What is the state of the package from SQL*Plus before and after you hit it using SQL*Plus and VB? In 9i the package is compiled on first hit - same as 8i - so I would not expect errors in respect to compilation.

Regards,
Vivek

 

[spicysudhi]

I look in Toad, I can see the red cross (invalid state) against the procedure.
if i execute from Sql plus, after execution, it becomes valid state.
if i call from VB, i get error in VB and procedure state remains unchanged!

i know this is something odd, dont know why happening.

regards,
sudhi

 

[spischer]

I am experiencing the same issue. Did you find a fix for this issue?

 

[spicysudhi]

nope, i recompiled it. but now i am not facing this problem. even if they are in invalid state, when i call, its automatically compiling (as it was in doing in 8i)

strange.

regards,
sudhi