pl/sql loop

[jimmy80]

hi,

I'm using pl/sql to write a loop... basicaly this loop loops between two different times and deletes information between these two times.
I'm having a problem in that the loop increments in 5000's.. but this means that it is going past the hour for example when it reaches 095500 (9:55) and increments it goes to 096000(9:60) instead of 100000(10:00). I was wondering is there anyway to get around this problem?

Basically what I have is this:
while start_time < end_time loop
delete from thetable

start_time + start_time + 5000
end loop.

Thanks for your help,

 

[rrrkrishnan]

try this

while start_time < end_time loop
delete from thetable

start_time = start_time + 5000
if start_time mod 10000 = 6000
then start_time = start_time + 4000
end if;
end loop.

 

[jee]

You could covert your times into Oracle dates for looping and increment by 1/288 (adding 1 to a date variable adds 1 day, so to add five minutes, add 1/288). Then in your loop convert the Oracle date back to your time string for deletion.