Disconnecting the network calls the stored procedure. Request for communication with the database on Hang Forever
Several stored procedures I support for querying remote databases over the WAN. The network sometimes goes down, but the worst thing that ever happened was procedures that were not running and needed to be restarted.
It has taken an ominous turn in the past couple of weeks. Instead of not performing procedures, it hangs in a strange state. They cannot be killed inside Oracle, and as long as they exist, any attempt to run other copies of the procedure will also hang. The only solution we have found is to kill the offending "kill -9" routines from the OS. Some of these routines have not changed for months, even years, so I suspect that this is due to the DB or DB configuration.
Anyone have any ideas on what we can do to fix the problem? Or does PL / SQL have a timeout mechanism that I can add to the code so that I can throw an exception that I can handle programmatically?
source to share
What version of the database? Are they stuck running SQL or PL / SQL? Has anyone been adding exception handling to routines lately? I remember that in 9iR2 we were told that instead, or by throwing an exception for the calling procedure, we had to catch all exceptions and keep working (basically try to run all items in the job, even if some don't work). We inevitably made the job loop in an infinite loop with SQL errors, hitting the exception handler and trying again. And they couldn't be killed because WHEN OTHERS also caught your session killed exception. I think the latter changed in 10g, so no exception was thrown.
source to share