CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
Antoine
Antoine

Reputation: 55

'Different results for the same function called from SQL Developer UI and in SQL query

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;

Upvotes: 1

Views: 215

Answers (1)

Antoine
Antoine

Reputation: 55

After many calls of both way to use the function. I reach to the conclusion that the element inducing the anomaly comes from the ue of 

to_date(v_date, 'DD/MM/YYYY')

where v_date is already declared as a date format behave in an inpredictable way. I decided to keep the date variable as such, and only use to_date for different cariable format.

I am not able to explain why exactly, but it solved my problems concerning dates behavior. Now I can call a function with data parameter from DUAL or from the SQL developer interface and get identical and coherent results.

Upvotes: 1

Related Questions