How to write a query that takes more than 2 minutes to execute

[Ravi Kiran Va]

Hi ,

I am writing a small program for Testing purpose , in that my requirement is to write a query that takes more than 2 minutes to execute .Right now i have only a small Table called as "Users" with very less data .

Please let me know how can i achieve this thing ??

Thanks .

[Jaikiran Pai]

I don't think this is related to EJBs. Can you explain a bit more before we move it to an appropriate forum?

[Ravi Kiran Va]

Basically i am testing as that ,, if the query takes more than 2 Minutes to execute , i need to stop executing it and display an Error Page in JSP (J2EE )

So please tell me , how can i achieve this . Thanks in advance .
how can i use

dbms_lock.sleep(120) with a basic query as "Select Names from Users" .

[Paul Sturrock]

How do you stop it? The only dependable way I know is to implement query timeouts in the database itself (particularly as you appear to be using Oracle) - in which case why test it?

If you want to have the client try to cancel a long running query (which might not work) you could test it by writing a query that opens a cursor that loops forever.

[Rob Spoor]

Can you create stored procedures? If so, just write an infinite loop in there:

WHILE 1=1
BEGIN
END

That should work.

[Bill Karwin]

What brand of database is this? (MySQL, Oracle, Microsoft SQL Server, etc.)

In MySQL, you can do: SELECT SLEEP(180) to make a query that take three minutes.

If you are using another brand of database, perhaps it has a similar function.

[Ireneusz Kordal]

Ravi wrote:
how can i use

dbms_lock.sleep(120) with a basic query as "Select Names from Users" .

You can wrap dbms_lock.sleep in a function:

CREATE OR REPLACE FUNCTION SLEEP( SECONDS IN NUMBER )
RETURN NUMBER
IS
BEGIN
  DBMS_LOCK.SLEEP( SECONDS );
  return SECONDS;
END;
/

set timings on;
SELECT sleep( 10 ) from DUAL;

SLEEP(10)              
---------------------- 
10                     

10 018 ms elapsed

[David Newton]

This is the wrong approach to take.

Your goal is to test the response of a query taking too long--don't actually *make* the query, plug in a service implementation that simulates a long-running query.