F is a function counting the number of weekdays betwwen two dates (using the format to_date(a, 'DD/MM/YYYY') [NLS_DATE_LANGUAGE = "FRENCH"].
It is stored in package P.
With a is the beginning date of the period and b the end.
When I use the SQL developer to run the function i.e. Right click on package p, execute, choose f function and fill the arguments:
if a = to_date('02/08/2019', 'DD/MM/YYYY') and b = to_date('12/08/2019', 'DD/MM/YYYY'))
I get a number of days = 7, which is the correct result.
But when I run it in SQL:
select p.f('02/08/2019','12/08/2019') from DUAL
I get a number of days = 8 and this result is obviously wrong.
I cannot understand how these two process can return two different values since the same F function is called, and the arguments are exactly the same.
I have been careful about the date format, still the two ways of calling f won't return the same result.
For information, this is the content of this function (sorry, cariable are expressed in french)
FUNCTION getjoursouvres
(
i_debut IN DATE,
i_fin IN DATE
)
RETURN NUMBER IS o_result NUMBER;
v_jour date;
v_nbjours NUMBER;
v_testferie number;
v_testweekend number;
v_testglobal number;
-- This cursor browses all dates between i_debut and i_fin (included)
cursor cx is
select to_date(i_debut, 'DD/MM/YYYY') + rownum -1 dt
from dual
connect by level <= to_date(i_fin, 'DD/MM/YYYY') - to_date(i_debut, 'DD/MM/YYYY') + 1;
BEGIN
open cx;
v_nbjours := 0;
loop
-- Browses all the days in the interval (begining and end included)
fetch cx into v_jour;
exit when cx%NOTFOUND;
-- testferie return 1 if the day is NOT a holiday, 0 if it is (so not be be added)
v_testferie := testferie(v_jour);
-- testweekend return 1 if the day is a weekday, 0 if it is on weekend (so not be be
v_testweekend := testweekend(v_jour);
--
v_testglobal := v_testferie + v_testweekend;
-- If v_testglobal = 2 then the day is neither weekend nor holiday. Therefore it is aded to the sum of days
if v_test = 2 then
v_nbjours := v_nbjours + 1;
end if;
end loop;
o_result := v_nbjours;
close cx;
return o_result;
END;
f.select f.p(a,b) from DUAL- what areaandbin that case?