How to set timeout for anonoymous block or query in plsql?

Question

I know you can set user profiles or set a general timeout for query.

But I wish to set timeout to a specific query inside a procedure and catch the exception, something like :

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 this possible? is there any timeout exceptions at all I can catch? per block or query? didn't find much info on the topic.

user_11597373 · Answer

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;

user_15756299 · Answer

MariaDB> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.002 sec)

MariaDB>

See: https://mariadb.com/kb/en/sleep/

How to set timeout for anonoymous block or query in plsql?

5 Answers