Question
Is there some way in Oracle SQL to simulate long running query ?
Why?
I want to test jdbc timeouts on Tomcat server from Java. Ideally I want some view that calls proc/function which would be sleep for defined time.
Problem
I have no access to use dbms_lock procedure, so I have to use something else.
So, i have a solution for this.
Solution
I have created VIEW in the database, which calls my func.
View:
create or replace VIEW TEST_VIEW (ID) as SELECT TEST_PROC() as ID from dual;
Func:
create or replace function TEST_PROC return number is
IS
start_time DATE := sysdate;
end_time DATE;
curr_time: DATE;
id number :=0;
begin
end_time := start_time + interval '30' minute; // here you may define time as you need.
loop id := id + 1;
curr_time := sysdate;
exit when curr_time > end_time;
end loop;
return id;
end TEST_PROC
Maybe that's would be helpful for someone.
BR.