For Loop w/time interval

[artm...@gmail.com]

Hi,

I need to write a FOR LOOP which uses minutes as the interval, from a start time to and end time.

So, maybe something like this (pseudo english):

For x IN (8:30 - 3:00 INTERVAL 1 minute) LOOP
.
.
.
END LOOP

Where X will be the HH:MI that the loop is performing on. I'll need access to that HH:MI to compare it against other things.

But, how to create the loop which will use the hour / minute?

Thanks!

[Mladen Gogala]

This should be facilitated using DBMS_LOCK.SLEEP routine, but it isn't as
simple as that.



--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com

[kooro...@gmail.com]

Something like this (with refinement):
SET SERVEROUTPUT ON
DECLARE
BEGIN
FOR xx IN (SELECT (TRUNC(SYSDATE) + 8.5/24)+rownum/24/60 The_time
FROM DUAL connect by level <= ((TRUNC(SYSDATE) + 9/24)-(TRUNC(SYSDATE) + 8.5/24))*60*24) LOOP

dbms_output.put_line(to_char(xx.the_time,'HH:MI'));

END LOOP;
END;
/

[Gerard H. Pille]

kooro...@gmail.com wrote:
> On Tuesday, June 18, 2013 1:22:23 AM UTC+10, artm...@gmail.com wrote:
>> Hi,
>>
>>
>>
>> I need to write a FOR LOOP which uses minutes as the interval, from a start time to and end time.
>>
>>
>>
>> So, maybe something like this (pseudo english):
>>
>>
>>
>> For x IN (8:30 - 3:00 INTERVAL 1 minute) LOOP
>>
>> .
>>
>> .
>>
>> .
>>
>> END LOOP
>>
>>
>>
>> Where X will be the HH:MI that the loop is performing on. I'll need access to that HH:MI to compare it against other things.
>>
>>
>>
>> But, how to create the loop which will use the hour / minute?
>>
>>
>>
>> Thanks!
>
> Something like this (with refinement):

Something like this (without troubling the database)?

declare
v_start date := trunc(sysdate) + 3/24;
v_end date := trunc(sysdate) + 8.5/24;
v_hhmm varchar2(5);
begin
while v_start <= v_end loop
v_hhmm := to_char(v_start,'HH24:MI');
dbms_output.put_line(v_hhmm);
v_start := v_start + 1/(24*60);
end loop;
end;
/

[ddf]

Unfortunately with that implementation the output won't be displayed until the loop has completed execution; if the OP wants a near-real-time display that won't work. Even a pipelined function won't work as it appears the loop generates values faster than the clock actually runs. Adding a sleep only delays the entire output set.

I haven't had much time to play with this to see if there is a way to implement what the OP wants but from casual inspection it appears there is not.

Maybe someone can prove me wrong.


David Fitzjarrell