We have an oracle procedure which is inside a package. When we are calling that Procedure from .NET (Windows Forms) it is giving Operation timeout errors
After seeing this message "Operation Timeout Error" from the screen,when i go to SqlDeveloper and Compile All Packages then after compiling them, the procedure works very fast and i can retrieve the data in 5 seconds.
Can you anyone tell how to solve this issue and what is the root cause?
Asked
Active
Viewed 705 times
0
habeeb afvan
- 99
- 1
- 1
- 4
-
Did you try another Procedure on. Net – CompEng Dec 03 '16 at 08:31
-
Maybe a duplicate of https://stackoverflow.com/questions/10352300/use-gvsession-to-tell-if-a-query-is-hanging – William Robertson Dec 03 '16 at 08:31
2 Answers
0
You should proabbly analyse why package is getting invalid and need compilation before executing and either eliminate code taht causes invalidation or as previous answer says execute compile before running procedure.
Package can get invalid when you alter some dependent objects (swaping table names or synonyms during some load, altering table, dynamically droping and creating objects that package depends on). Those two threads may help you: What Situations Cause Oracle Packages to Become Invalid? and When does an Oracle Package Specification become INVALID If you're able to eliminate package invalidation it is better if you can't go for compilation before each run of procedure.
One more cause that calling procedure from .NET is not recompiling package can be driver. I met such issue a few years ago and remember that changing driver helped. But I don't remember if we changed to managed or unamanged driver.
