How to set timeout for anonymous block or request in plsql?

Question

I know that you can set user profiles or set a common timeout for the request.

But I want to set a timeout for a specific request inside the procedure and catch an exception, for example:

begin update tbl set col = v_val; --Unlimited time delete from tbl where id = 20; --Unlimited time begin delete from tbl; -- I want this to have a limited time to perform exception when (timeout???) then --code; end; end; 

Is it possible? are there any timeout exceptions on all that i can catch? per block or request? did not find much information on this topic.

Answer 1

No, you cannot set a timeout in the pl / sql file. You can use the host language for this, in which you insert your sql and pl / sql.

Answer 2

Just an idea: you can do Delete in DBMS_JOB . Then create a procedure to monitor the task, which then calls:

 DBMS_JOB.BROKEN(JOBID,TRUE); DBMS_JOB.remove(JOBID); 

Answer 3

You can do:

 select * from tbl for update wait 10; --This example will wait 10 seconds. Replace 10 with number of seconds to wait 

Then the selection will try to block the specified lines, but if it fails after n seconds, it will throw out "ORA-30006: busy resource, WAIT timed out". If the lock is reached, you can perform the deletion.

Hope this helps.

Answer 4

 v_timer1 := dbms_utility.get_time(); WHILE TRUE LOOP v_timer2 := dbms_utility.get_time(); EXIT WHEN (ABS(v_timer1 - v_timer2)/100) > 60; -- cancel after 60 sec. END LOOP;