SQL> create or replace function nbwdays (start_date date, nbdays integer) return integer
  2  is
  3    nbwdays integer;
  4  begin
  5    select count(*) into nbwdays
  6    from (select start_date+level-1 dt from dual connect by level <= nbdays)
  7    where to_char(dt,'Dy','NLS_DATE_LANGUAGE=AMERICAN') not in ('Sat','Sun');
  8    return nbwdays;
  9  end;
 10  /

Function created.

SQL> select nbwdays(sysdate, 10) from dual;
NBWDAYS(SYSDATE,10)
-------------------
                  8

1 row selected.

SQL> select nbwdays(sysdate, 20) from dual;
NBWDAYS(SYSDATE,20)
-------------------
                 15

1 row selected.

SQL> create or replace function wday (start_date date, nbdays integer) return date
  2  is
  3    stdate date;
  4    endate date;
  5  begin
  6    -- Start date is next Monday if stat_date falls in a weekend (Saturday or Sunday)
  7    -- This uses the property that an ISO week (IW) starts a Monday
  8    stdate := greatest(start_date,trunc(start_date+2,'IW'));
  9    -- Add number of weeks as there are number of 5 days in "nbdays"
 10    endate := stdate + 7 * trunc(nbdays/5);
 11    -- Add rest of days
 12    endate := endate + mod(nbdays,5);
 13    -- If end date falls in a weekend go to next week
 14    endate := endate + case when trunc(endate+2,'IW') = trunc(endate,'IW') then 0 else 2 end;
 15    return endate;
 16  end;
 17  /

Function created.

SQL> select level, WDAY(sysdate,level) from dual connect by level <= 10;
     LEVEL WDAY(SYSDAT
---------- -----------
         1 10-DEC-2024
         2 11-DEC-2024
         3 12-DEC-2024
         4 13-DEC-2024
         5 16-DEC-2024
         6 17-DEC-2024
         7 18-DEC-2024
         8 19-DEC-2024
         9 20-DEC-2024
        10 23-DEC-2024

10 rows selected.

SQL> select level, WDAY(sysdate+level,1) from dual connect by level <= 10;
     LEVEL WDAY(SYSDAT
---------- -----------
         1 11-DEC-2024
         2 12-DEC-2024
         3 13-DEC-2024
         4 16-DEC-2024
         5 17-DEC-2024
         6 17-DEC-2024
         7 17-DEC-2024
         8 18-DEC-2024
         9 19-DEC-2024
        10 20-DEC-2024

10 rows selected.

SQL> select WDAY(to_date('12/9/2024','MM/DD/YYYY'),6) from dual;
WDAY(TO_DAT
-----------
17-DEC-2024

1 row selected.

SQL> select  date '9999-12-31' - trunc(sysdate) max_possible_n
  2    from  dual
  3  /

MAX_POSSIBLE_N
--------------
       2912830

SQL> set timing on
SQL> select  count(*)
  2    from  dual
  3    where to_char(trunc(sysdate),'Dy','NLS_DATE_LANGUAGE=AMERICAN') not in ('Sat','Sun')
  4    connect by level <= 20
  5  /

  COUNT(*)
----------
        20

Elapsed: 00:00:00.65
SQL> select  count(*)
  2    from  dual
  3    where to_char(trunc(sysdate) + level - 1,'Dy','NLS_DATE_LANGUAGE=AMERICAN') not in ('Sat','Sun')
  4    connect by level <= 2912830
  5  /

  COUNT(*)
----------
   2080594

Elapsed: 00:00:04.80
SQL>

SQL> select  count(*)
  2    from  dual
  3    where to_char(trunc(sysdate) + level - 1,'Dy','NLS_DATE_LANGUAGE=AMERICAN') not in ('Sat','Sun')
  4    connect by level <= 1
  5  /

  COUNT(*)
----------
         1

Elapsed: 00:00:00.00
SQL>

trunc(start_dt,'iw')     - start date week start
trunc(start_dt + n,'iw') - end date (start date + n) week start

(trunc(start_dt + n,'iw') - trunc(start_dt,'iw')) / 7 full weeks between start date week start and end date week start
((trunc(sysdate + n,'iw') - trunc(sysdate,'iw')) / 7) * 5 weekdays in full weeks

least(trunc(sysdate) - trunc(sysdate,'iw'),4) - first week weekdays before start date
least(trunc(sysdate + n) - trunc(sysdate + n,'iw'),4) + 1  - last week weekdays before or equal to end date

Query:

select  ((trunc(sysdate + n,'iw') - trunc(sysdate,'iw')) / 7) * 5 - least(trunc(sysdate) - trunc(sysdate,'iw'),4) +
        least(trunc(sysdate + n) - trunc(sysdate + n,'iw'),4) + 1 weekdays
  from  dual
/

SQL> select  ((trunc(sysdate + 20,'iw') - trunc(sysdate,'iw')) / 7) * 5 - least(trunc(sysdate) - trunc(sysdate,'iw'),4) +
  2          least(trunc(sysdate + 20) - trunc(sysdate + 20,'iw'),4) + 1 weekdays
  3    from  dual
  4  /

  WEEKDAYS
----------
        15

Elapsed: 00:00:00.01
SQL> select  ((trunc(sysdate + 2912830,'iw') - trunc(sysdate,'iw')) / 7) * 5 - least(trunc(sysdate) - trunc(sysdate,'iw'),4) +
  2          least(trunc(sysdate + 2912830) - trunc(sysdate + 2912830,'iw'),4) + 1 weekdays
  3    from  dual
  4  /

  WEEKDAYS
----------
   2080595

Elapsed: 00:00:00.00
SQL>

CREATE TABLE  CALENDAR
(
    CALENDER_DATE    DATE PRIMARY KEY,
    WEEK_DAY      NUMBER (1) NOT NULL, 
    CREATED_DT      DATE DEFAULT SYSDATE
);


CREATE INDEX IX_CALENDAR_DAY_WEEK_DAY
    ON  CALENDAR (WEEK_DAY);
    
 

 INSERT INTO  CALENDAR (CALENDER_DATE, 
                      WEEK_DAY)
    WITH
         
        ALL_DTS
        AS
            (    SELECT DATE '2010-01-01' + LEVEL - 1     AS DT
                   FROM DUAL
             CONNECT BY DATE '2010-01-01' + LEVEL - 1 <= DATE '2110-01-01')
    SELECT D.DT,
         CASE WHEN  TO_CHAR (D.DT, 'fmDy', 'NLS_DATE_LANGUAGE=ENGLISH')

                                      IN ('Sat', 'Sun')

                                  THEN  0

                                  ELSE  1

                              END 
      FROM ALL_DTS  D;
	  
/

CREATE TABLE HOLIDAYS
(
    ID                 NUMBER (5) GENERATED AS IDENTITY PRIMARY KEY,
    HOLIDAY_DESC       VARCHAR (100),
    HOLIDAY_DATE       DATE not null, 
    CREATED_DT       DATE DEFAULT SYSDATE
);
 
 
CREATE INDEX IX_HOLIDAYS_HOLIDAY_DATE ON HOLIDAYS(HOLIDAY_DATE)

/

	  
-- View to combine holiday and workday table for easy map	  
 CREATE OR REPLACE FORCE VIEW V_CALENDAR
(
    CALENDER_DATE,
     WEEK_DAY
)
 
AS
    SELECT CALENDER_DATE,
           CASE WHEN H.HOLIDAY_DATE is not null THEN 0 ELSE  WEEK_DAY END    AS WEEK_DAY
      FROM CALENDAR  C
           LEFT JOIN HOLIDAYS H ON C.CALENDER_DATE = H.HOLIDAY_DATE
    UNION
    SELECT H.HOLIDAY_DATE, 0
      FROM  HOLIDAYS H
  


 
/

CREATE OR REPLACE FUNCTION Get_next_working_day (
    START_DATE   IN DATE,
    N_DAYS       IN PLS_INTEGER DEFAULT 1)
    RETURN DATE
AS
   
    NON_WORKING_DAYS   INT := 0;
    OUTPUT_DATE         DATE;
    COUNTER            INT := 0;
BEGIN
    SELECT COUNT (*)
      INTO NON_WORKING_DAYS
      FROM V_CALENDAR D
     WHERE     CALENDER_DATE BETWEEN TRUNC (START_DATE)
                                 AND TRUNC (START_DATE + N_DAYS)
           AND IS_WEEK_DAY = 0;

    SELECT TRUNC ((START_DATE + N_DAYS + NON_WORKING_DAYS))
      INTO OUTPUT_DATE
      FROM DUAL;
 
    BEGIN
        LOOP
            SELECT COUNT (1)
              INTO COUNTER
              FROM V_CALENDAR D
             WHERE     TRUNC (D.CALENDER_DATE) = TRUNC (OUTPUT_DATE)
                   AND WEEK_DAY = 0; 
            IF COUNTER = 0
            THEN
                EXIT;
            END IF;

            -- Increment counter
            COUNTER := COUNTER + 1;
            OUTPUT_DATE := FINAL_DATE + 1;
           
        END LOOP;
    END;

    RETURN OUTPUT_DATE;
END Get_next_working_day;
/

SQL> CREATE TABLE HOLIDAYS
  2  (
  3      ID                 NUMBER (5) PRIMARY KEY,
  4      HOLIDAY_DESC       VARCHAR (100),
  5      HOLIDAY_DATE       DATE not null,
  6      CREATED_DT       DATE DEFAULT SYSDATE
  7  );

Table created.

SQL> insert into HOLIDAYS (ID, HOLIDAY_DATE) values (1, to_date('12/24/2024','MM/DD/YYYY'));

1 row created.

SQL> insert into HOLIDAYS (ID, HOLIDAY_DATE) values (2, to_date('12/25/2024','MM/DD/YYYY'));

1 row created.

SQL> insert into HOLIDAYS (ID, HOLIDAY_DATE) values (3, to_date('1/1/2025','MM/DD/YYYY'));

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace function wday (start_date date, nbdays integer) return date
  2  is
  3    wday date;
  4  begin
  5    with
  6      days as (
  7        select level rn,
  8               case
  9                 when to_char(start_date+level,'Dy','NLS_DATE_LANGUAGE=AMERICAN') in ('Sat','Sun') then null
 10                 when (start_date+level) in (select holiday_date from holidays) then null
 11                 else 'WDAY'
 12               end wdflag
 13        from dual
 14        connect by level <= 7*ceil(nbdays/5)+30
 15      ),
 16      wdays as (
 17        select rn,
 18               count(wdflag) over (order by rn) nbwdays
 19        from days
 20      )
 21    select start_date + rn into wday
 22    from wdays
 23    where nbwdays = nbdays;
 24    return wday;
 25  end;
 26  /

Function created.

SQL> select wday(to_date('12/12/2024','MM/DD/YYYY'), 15) from dual;
WDAY(TO_DAT
-----------
07-JAN-2025

1 row selected.

SQL>   with
  2      days as (
  3        select level rn,
  4               case
  5                 when to_char(to_date('12/12/2024','MM/DD/YYYY')+level,'Dy','NLS_DATE_LANGUAGE=AMERICAN') in ('Sat','Sun') then null
  6                 when (to_date('12/12/2024','MM/DD/YYYY')+level) in (select holiday_date from holidays) then null
  7                 else 'WDAY'
  8               end wdflag
  9        from dual
 10        connect by level <= 7*ceil(15/5)+30
 11      )
 12  select rn,
 13         to_date('12/12/2024','MM/DD/YYYY') + rn curdate,
 14         wdflag,
 15         count(wdflag) over (order by rn) nbwdays
 16  from days
 17  /
        RN CURDATE     WDFL    NBWDAYS
---------- ----------- ---- ----------
         1 13-DEC-2024 WDAY          1
         2 14-DEC-2024               1
         3 15-DEC-2024               1
         4 16-DEC-2024 WDAY          2
         5 17-DEC-2024 WDAY          3
         6 18-DEC-2024 WDAY          4
         7 19-DEC-2024 WDAY          5
         8 20-DEC-2024 WDAY          6
         9 21-DEC-2024               6
        10 22-DEC-2024               6
        11 23-DEC-2024 WDAY          7
        12 24-DEC-2024               7
        13 25-DEC-2024               7
        14 26-DEC-2024 WDAY          8
        15 27-DEC-2024 WDAY          9
        16 28-DEC-2024               9
        17 29-DEC-2024               9
        18 30-DEC-2024 WDAY         10
        19 31-DEC-2024 WDAY         11
        20 01-JAN-2025              11
        21 02-JAN-2025 WDAY         12
        22 03-JAN-2025 WDAY         13
        23 04-JAN-2025              13
        24 05-JAN-2025              13
        25 06-JAN-2025 WDAY         14
        26 07-JAN-2025 WDAY         15
...

SQL> create or replace function wday (start_date date, nbdays integer) return date
  2  is
  3    wday date;
  4  begin
  5    with
  6      days as (
  7        select level rn,
  8               case
  9                 when to_char(start_date+level,'Dy','NLS_DATE_LANGUAGE=AMERICAN') in ('Sat','Sun') then null
 10                 when (start_date+level) in (select holiday_date from holidays) then null
 11                 else 'WDAY'
 12               end wdflag
 13        from dual
 14        connect by level <= 7*ceil(nbdays/5)+30
 15      ),
 16      wdays as (
 17        select rn, wdflag,
 18               count(wdflag) over (order by rn) nbwdays
 19        from days
 20      )
 21    select start_date + rn into wday
 22    from wdays
 23    where nbwdays = nbdays and wdflag is not null;
 24    return wday;
 25  end;
 26  /

Function created.