2

I wrote a generic SQL select statement executor with "Groovy-SQL" (=Java JDBC Wrapper). It has got a timeout feature.

To test the timeout feature in an automated integration test I'd like to issue a SQL select like 

SELECT sleep(10) from DUAL;

which should run for 10 seconds and then return. Like MySql's http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_sleep.

Is there something like this also in Oracle to simulate a "long running" query? I'd like to have a plain SQL query, without any PL/SQL or special grants like in the Sleep function in ORACLE discussion.

Community
  • 1
  • 1
Peti
  • 1,670
  • 1
  • 20
  • 25
  • 2
    I can think of one terrible thing: try to find a regular expression that triggers catastrophic backtracking and use it in a `REGEXP_LIKE`. You won't be able to regulate the timeout, and your DBA is going to hate you if you succeed, but it *would* be SQL only... – Jeroen Mostert Jun 16 '16 at 07:31
  • Very ugly but very cool idea @JeroenMostert! – Peti Jun 16 '16 at 07:32
  • 1
    Please, *please* don't use that on a production system though. The resulting CPU spike would not be pretty. (I assume Oracle has a way of shooting runaway threads, but I have no experience.) – Jeroen Mostert Jun 16 '16 at 07:34
  • Sure, @JeroenMostert. I would issue it on a test instance and abort the statement after let's say 1 second. The interesting part here will be if Oracle can internally really kill (interrupt) the slow regex. – Peti Jun 16 '16 at 07:36

1 Answers1

2

Use dbms_lock.sleep()

begin
   dbms_lock.sleep(10);
end;

This can be execute JDBC if you pass the whole PL/SQL block as a single string: 

String sleep = "begin dbms_lock.sleep(10); end;";
Statement stmt = connection.createStatement();
stmt.execute(sleep);

https://docs.oracle.com/database/121/ARPLS/d_lock.htm#ARPLS66782

This does however call a PL/SQL procedure so it doesn't really qualify as "without stored functions".