HI All - Thanks in advance for your help. We are seeing incorrect result the first time the connect by level is used in the function in Oracle 19c version(19.7 April 2020 RU). When the query is executed the second time, it is giving correct result. We don't see this issue in Oracle 12.2.0.1 version.
The function description is listed in the function definition.
Thanks.
1) create table
CREATE TABLE HOLIDAY
( HOLIDAY_DT DATE NOT NULL,
HOLIDAY_DESC VARCHAR2(250 BYTE)
)
;
2) Insert data into HOLIDAY table
SET DEFINE OFF;
Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('1/1/2020', 'MM/DD/YYYY'), 'New Year''s Day');
Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('1/20/2020', 'MM/DD/YYYY'), 'Martin Luther King, Jr. Day');
Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('2/17/2020', 'MM/DD/YYYY'), 'Presidents'' Day');
Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('5/25/2020', 'MM/DD/YYYY'), 'Memorial Day ');
Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('7/3/2020', 'MM/DD/YYYY'), 'Independence Day');
Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('9/7/2020', 'MM/DD/YYYY'), 'Labor Day');
Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('10/12/2020', 'MM/DD/YYYY'), 'Columbus Day');
Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('11/11/2020', 'MM/DD/YYYY'), 'Veterans'' Day');
Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('11/26/2020', 'MM/DD/YYYY'), 'Thanksgiving Day');
Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('12/25/2020', 'MM/DD/YYYY'), 'Christmas Day');
COMMIT;
3) Create function
CREATE OR REPLACE FUNCTION "TESTHOLIDAYSWKENDS" (v_start_dt in varchar2, v_end_dt in varchar2)
RETURN date
IS
v_new_end_dt date;
/*
Description: This function receives a start date and end date as input and a new end date is returned. It will
calculate for a two business day lag and if a holiday or weekend falls within the two-day lag, it will
search for the previous business day and then return the new date to the calling program.
*/
BEGIN
WITH date_tab AS
(SELECT TO_DATE (v_start_dt, 'mm/dd/yyyy') + LEVEL - 1 business_date
FROM DUAL
CONNECT BY LEVEL <=
TO_DATE (v_end_dt, 'mm/dd/yyyy')
- TO_DATE (v_start_dt, 'mm/dd/yyyy')
+ 1)
,
business_date AS
(SELECT business_date
FROM date_tab
WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN')
AND business_date not in (select holiday_dt from HOLIDAY)
ORDER BY 1 DESC
)
SELECT business_date into v_new_end_dt FROM
(
SELECT business_date FROM business_date
WHERE rownum <= 3
minus
SELECT business_date FROM business_date
WHERE rownum <= 2
);
RETURN(v_new_end_dt);
END;
/
4) Run the SELECT statement
sql>SELECT to_char(nvl(TESTHOLIDAYSWKENDS('07/16/2020','07/29/2020'), TO_DATE('12/31/9999','mm/dd/yyyy')), 'mm/dd/yyyy') as End_Dt FROM DUAL;
END_DT
----------
07/24/2020 <-------(incorrect result when run the first time)
sql> /
END_DT
----------
07/27/2020 <-------(correct result when re-run immediately)
sql> alter system flush shared_pool;
System altered.
sql>SELECT to_char(nvl(TESTHOLIDAYSWKENDS('07/16/2020','07/29/2020'), TO_DATE('12/31/9999','mm/dd/yyyy')), 'mm/dd/yyyy') as End_Dt FROM DUAL;
END_DT
----------
07/24/2020 <-------(incorrect result when run the first time)
sql> /
END_DT
----------
07/27/2020 <-------(correct result when re-run immediately)
Thanks.