CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
Khamis Khalil
Khamis Khalil

Reputation: 11

Set timeout for PLSQL block or query inside procedure?

I know we can set timeout using resource manager at schema level. But I want to set timeout to a specific query inside a procedure and catch the exception. lets say i want to stop query execution for specific query after 15 secs. Is there any way for this? Or any timeout exception available? I am using Oracle Db 12.2. Thanks.

Upvotes: 1

Views: 1184

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

As far as I can tell, there's no such thing.

However, see if this helps:

  • instead of running the procedure itself, schedule it (using dbms_scheduler)
  • then, find the job by querying dba_scheduler_running_jobs
  • finally, if you think it runs too long, kill it (using dbms_scheduler.stop_job)

Yes, I know - you're talking about a query within a procedure. It makes things more complex as the rest of the procedure should wait for that particular query (now transformed to job) to finish.

Though, if it were a loop, you could set a timer and check it in every loop iteration and then exit the loop if it runs longer than 15 seconds. Maybe you can make that query to use condition you'd put into e.g. cursor FOR loop's where clause ...

Upvotes: 1

Related Questions