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;
/