CODEWITHSUNDEEP
javaoracle-databasejdbc
brightmatter
brightmatter

Reputation: 192

Write an Oracle query that runs for a specific amount of time before returning

I have been looking for a testing query. I want the database to delay a response until after some time has passed. I imagine a query that would wait 30 seconds before replying to look like this: SELECT SYSDATE FROM DUAL DELAY(30, 'second')

I don't want it to be a burden to the database, but I do want it to force my Java code to deal with long running transactions to the DB.

Any ideas?

Upvotes: 2

Views: 1604

Answers (1)

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

You can create pl/sql function and invoke it as part of the query. The function should have the logic to sleep for what ever time you want.

Here is the working code (the schema/user needs to have access on dbms_lock package).

create or replace function func
return number
as
begin
  dbms_lock.sleep(60);
  return 0;
end;
/

Function created.

SQL> set timing on
SQL> select func from dual;

      FUNC
----------
     0

Elapsed: 00:01:00.01

Upvotes: 10

Related Questions