PL SQL Function while loop ending unexpectedly

Question

i have a function where i want to calculate seconds between two dates these seconds should be calculated only in our companys opening hours. because it can take several days for the task to be completed i have created a while loop that should increment the days

here is my loop:

    WHILE TO_DATE(TRUNC(dateIterator),'YYYY-MM-DD') < TO_DATE(TRUNC(CallbackUpdatedDate),'YYYY-MM-DD') LOOP
 calculateIntervalFrom := null;
calculateIntervalTo := null;

SELECT THIS_DATE_OPENING,
 THIS_DATE_CLOSING,
 NEXT_DATE_OPENING
INTO thisDateOpening, thisDateClosing, nextDay
FROM KS_DRIFT.SYS_DATE_KS
WHERE THIS_DATE = dateIterator;

-- gets calculated from
calculateIntervalFrom := thisDateOpening;

-- Gets calculateTo
IF dateIterator = TO_DATE(TRUNC(CallbackUpdatedDate),'YYYY-MM-DD') THEN
calculateIntervalTo := CallbackUpdatedDate;
ELSE
calculateIntervalTo := thisDateClosing;
END IF;
fromSeconds := abs(extract(second from TO_TIMESTAMP(calculateIntervalFrom))) + extract(minute from TO_TIMESTAMP(calculateIntervalFrom)) * 60 + extract(hour from TO_TIMESTAMP(calculateIntervalFrom)) * 60 * 60;
toSeconds :=abs(extract(second from TO_TIMESTAMP(calculateIntervalTo))) + extract(minute from TO_TIMESTAMP(calculateIntervalTo)) * 60 + extract(hour from TO_TIMESTAMP(calculateIntervalTo)) * 60 * 60;
intervalDifference := fromSeconds - toSeconds;

solvedSeconds := solvedSeconds + intervalDifference;
dateIterator := nextDay;
dbms_output.put_line(nextDay);

A small explanation

So The idea is that as long as the value dateIterator (which is the date of which the row is created) is not the same as last time the row was updated (CallbackUpdatedDate) it will select today and the next daysopening hours from the table KS_DRIFT.SYS_DATE_KS

If the day today is the same as the CallbackUpdatedDate it will set the calculateIntervalTo equal to the CallbackUpdatedDate else it will set the value to thisDateClosing

it will then calculate the seconds between the two dates and add them to the NUMBER solvedSeconds

After that i will set dateIterator to the next opening day: dateIterator := nextDay;

This works but only twice and then it simply ends the loop:

Here is the two dates it trys to compute:

Created: 2013-03-26 10:23:33

Last updated: 2013-04-03 09:25:10

dbms_output.put_line(nextDay); outputs these two values:

2013-03-27 and 2013-03-27

By the looks of it, it would seem that the SELECT statement within the while loop does not work as intended but i simply cannot see why?

Thorsten Kettner · Accepted Answer · 2013-04-11 14:56:43Z

2

It looks strange how you treat the dates:

TO_DATE(TRUNC(dateIterator),'YYYY-MM-DD')

So dateIterator is a date which you trunc, i.e. you remove the time part. This gives you a date with time set to 00:00:00. But then you use the to_date function on that date as if it were a string. This looks wrong to me.

Then in your select statement you have

WHERE THIS_DATE = dateIterator

and I wonder: Above you had to strip the time from dateIterator. Here you don't. Does your table have entries for the dateIterator incuding the time part?

I hope these findings already help solve the problem.

answered Apr 11, 2013 at 14:56

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user359040 · Accepted Answer · 2013-04-11 14:55:10Z

1

Rather than using a WHILE loop, why not just use a single query? Something like:

select (end_date - start_date - 
        (select coalesce(sum(least(NEXT_DATE_OPENING,end_date) -
                             greatest(THIS_DATE_CLOSING,start_date)),0)
         from KS_DRIFT.SYS_DATE_KS
         where THIS_DATE_CLOSING < end_date and 
               NEXT_DATE_OPENING > start_date)) * 86400 available_time
from dual

SQLFiddle here.

answered Apr 11, 2013 at 14:55

user359040

Collectives™ on Stack Overflow

PL SQL Function while loop ending unexpectedly

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related