Write a pure SQL query with PL/SQL that stop after :N seconds, where :N is a bind variable.
My solution
[sourcecode language=”sql”]
with v(start_hsecs, delta, flag) as (
select
hsecs as start_hsecs, 0 as delta, 1 as flag
from v$timer
union all
select
v.start_hsecs,
(t.hsecs-v.start_hsecs)/100 as delta,
case when (t.hsecs-v.start_hsecs)/100 > :N /* seconds */ then v.flag*-1 else v.flag+1 end as flag
from v, v$timer t
where v.flag>0 and t.hsecs>=v.start_hsecs
)
select delta
from v
where 0>flag
/
–end
[/sourcecode]
[collapse]
SQL> var N number
SQL> exec :N := 3 /* seconds */;
PL/SQL procedure successfully completed.
SQL> select...
DELTA
----------
3.01
1 row selected.
Elapsed: 00:00:03.01
Another my solution using sys.standard.current_timestamp, so some internal pl/sql…:
select count(*) from dual
connect by sys.standard.current_timestamp - current_timestamp <= interval'3'second;