PL/SQL: Identify dates of weekdays within time period using NEXT_DAY() function

create or replace package booking_dates_pkg as

  /* Loops through all weekdays and calls procedure save_dates
   *  @param pi_coach specifies the coach name
   *  @param pi_period_from specifies the start date in format DD.MM.YYYY
   *  @param pi_weekdays specifies the colon concatinated numbers of the weekdays, e.g. 0:4 = Monday and Friday
   *  @param pi_period_to specifies the end date in format DD.MM.YYYY
   *  @param pi_period_time specifies the daytime
  */
  procedure loop_through_weekdays (
    pi_coach         in varchar2
  , pi_period_from   in date
  , pi_weekdays      in varchar2
  , pi_period_time   in varchar2
  , pi_period_to     in date
  );

  /* Identifies each date of a passed weekday within the period
   *  @param pi_coach specifies the coach name
   *  @param pi_period_from specifies the start date in format DD.MM.YYYY
   *  @param pi_weekday specifies the number of the weekday, e.g. 0 = Monday
   *  @param pi_period_to specifies the end date in format DD.MM.YYYY
   *  @param pi_period_time specifies the daytime
  */
  procedure save_dates (
    pi_coach         in varchar2
  , pi_period_from   in date
  , pi_weekday       in number
  , pi_period_time   in varchar2
  , pi_period_to     in date
  );
    
end booking_dates_pkg;
/

create or replace package body booking_dates_pkg
as

  procedure loop_through_weekdays (
    pi_coach         in varchar2
  , pi_period_from   in date
  , pi_weekdays      in varchar2
  , pi_period_time   in varchar2
  , pi_period_to     in date
  )
  as
  begin

    for i in (select to_number(regexp_substr(pi_weekdays, '[^:]+', 1, level)) as day
                from dual 
          connect by to_number(regexp_substr(pi_weekdays, '[^:]+', 1, level)) is not null)
    loop
        save_dates(
	            pi_coach         => pi_coach
			      , pi_period_from   => pi_period_from
			      , pi_weekday       => i.day
			      , pi_period_time   => pi_period_time
			      , pi_period_to     => pi_period_to
		          );
    end loop;

  end loop_through_weekdays;

  procedure save_dates (
    pi_coach         in varchar2
  , pi_period_from   in date
  , pi_weekday       in number
  , pi_period_time   in varchar2
  , pi_period_to     in date
  )
  as
    l_day date;
    l_weekday_name varchar2(30);
    l_period_from_year number;
    l_period_to_year number;
    l_count_days number;
    l_count_holidays number;
  begin
  
    l_period_from_year := extract(year from pi_period_from);
    l_period_to_year   := extract(year from pi_period_to);
  
    /* count all weekdays of a single weekday (e.g. Monday) within the period
     * function next_day() depends of the invoking session's NLS_DATE_LANGUAGE and does not allow the specification of NLS values to make it independent
     * workaround: to make function next_day() independent, use trunc(sysdate, 'IW') to get the first day of week (Monday) defined by ISO standard,
     * and add number of days pi_weekday to identify the name of the selected day of the week uniquely
    */
    l_weekday_name := to_char(trunc(sysdate, 'IW') + pi_weekday, 'Day');

    l_count_days := ( ( next_day ( pi_period_to - 7, l_weekday_name )
                        - next_day ( pi_period_from - 1, l_weekday_name ) 
                      ) / 7 
                    ) + 1;
  
     for rec_days in 0..l_count_days-1
     loop

          /* identify the weekday (subtract 1 day to include the start date as well) and all following occurrences within the period by calculating weekday+7*rec_days */
          l_day := ( next_day ( pi_period_from - 1, l_weekday_name ) + 7 * rec_days );
        
          select count(holiday_date) into l_count_holidays
            from table(get_german_holidays(l_period_from_year, l_period_to_year))
           where holiday_date = l_day;
        
          if l_count_holidays = 0 then           
             insert into appointments(date, booked, coach)
                  values (to_date(to_char(l_day, 'DD.MM.YYYY') || ' ' || pi_period_time, 'DD.MM.YYYY HH24:MI'), 0, pi_coach);
          end if;
      
     end loop;

  end save_dates;
  
end booking_dates_pkg;
/