Why is APEX_COLLECTION.AddMember only adding the last index called of the outer loop, overwriting the previous indices?

I have an procedure that I am calling on APEX. The procedure has two parameters. The status is 'Operational'. Here is sample data for the i_schoolList clob parameter. It splits the data into the l_schoolNames table type:

Harmony
Crestview
Evergreen
Brightside
Summit
Willowbrook
Riverside
Valleyview
Oakridge
Meadowlark

Below is my procedure:

Procedure POPULATE_SCHOOLS(i_status     In Varchar2,
                           i_schoolList In Clob) Is
  
  -- Declare a collection type to store the split values
  TYPE schoolNameList IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;

  -- Declare a variable to hold the split values
  l_schoolNames schoolNameList;
  l_count       Number;
  
  -- Declare variables for column values
  l_region       CLONE_FINAL.REGION%TYPE;
  l_schoolName     CLONE_FINAL.school_NAME%TYPE;
  l_sector       CLONE_FINAL.SECTOR%TYPE;

  --Declare variables for error logging
  l_errorNumber  Number;
  l_parameters   Varchar2(4000);
  l_errorMessage Varchar2(4000);
  l_procedure    Varchar2(400);
  l_owner        Varchar2(100);

  --Temporary variables
  l_proc_name  VARCHAR2(100);
  l_error_info VARCHAR2(4000);
  l_log_data   CLOB;
    
  Cursor schoolRows(schoolName In Varchar2)
  Is
    Select region, 
           school_name, 
           sector
    From   S_SCHOOLS
    Where  school_NAME = schoolName;
    
Begin
  -- Calculate the number of occurrences of the delimiter
  l_count := REGEXP_COUNT(i_schoolList, CHR(10)) + 1;

  -- Iterate over the occurrences and extract the substrings
  FOR ind IN 1..l_count LOOP
    l_schoolNames(ind) := REGEXP_SUBSTR(i_schoolList, '[^' || CHR(10) || ']+', 1, ind);
  END LOOP;

  IF apex_collection.collection_exists(p_collection_name => 'SCHOOLS') THEN
    apex_collection.delete_collection(p_collection_name => 'SCHOOLS');
  END IF;


  --Create collection
  apex_collection.create_or_truncate_collection(p_collection_name => 'SCHOOLS');

  --Check if status is operational only. If it is, select from CLONE_FINAL table
  If (i_status = 'Operational')
  Then
    l_errorMessage := 'i.Count=' || l_schoolNames.Count        || CHR(13) || CHR(13) ||
                      'SQLERRM:'                             || CHR(13) || SQLERRM                || CHR(13) || CHR(13) ||
                      'DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:' || CHR(13) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
                        
    insert_log_entry(l_proc_name, l_parameters, l_errorMessage);
    
    --Loop through SCHOOLS to get data
    For schoolIndex In 1..l_schoolNames.Count
    Loop
    
      l_errorMessage := 'schoolIndex='         || schoolIndex         || CHR(13) || CHR(13) ||
                        'l_schoolNames.FIRST=' || l_schoolNames.FIRST || CHR(13) || CHR(13) ||
                        'l_schoolNames.LAST='  || l_schoolNames.LAST  || CHR(13) || CHR(13) ||
                        'l_schoolNames.COUNT=' || l_schoolNames.COUNT || CHR(13) || CHR(13) ||
                        'SQLERRM:'                                || CHR(13) || SQLERRM                || CHR(13) || CHR(13) ||
                        'DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:'    || CHR(13) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

      DBMS_OUTPUT.PUT_LINE('schoolIndex=' || schoolIndex || ' l_schoolNames(schoolIndex)=' ||  l_schoolNames(schoolIndex));

      insert_log_entry(l_proc_name, l_parameters, l_errorMessage);

      For dataRec In schoolRows(schoolName => l_schoolNames(schoolIndex))
      Loop
          DBMS_OUTPUT.PUT_LINE('l_schoolNames.Count=' || l_schoolNames.Count || ' schoolIndex=' ||  schoolIndex);

          l_errorMessage := 'schoolIndex='                           || schoolIndex                         || CHR(13) || CHR(13) ||
                            'schoolRows(l_schoolNames(schoolIndex)='    || l_schoolNames(schoolIndex)            || CHR(13) || CHR(13) ||
                            'SQLERRM:'                             || CHR(13) || SQLERRM                || CHR(13) || CHR(13) ||
                            'DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:' || CHR(13) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
                        
          insert_log_entry(l_proc_name, l_parameters, l_errorMessage);
          --Add the school to the apex collection to display on the Front end page 10922:5
          apex_collection.add_member(p_collection_name => 'SCHOOLS',
                                     p_c001            => dataRec.region,
                                     p_c002            => dataRec.school_name,
                                     p_c003            => dataRec.sector);
      End Loop;
    End Loop;
  End If;
Exception
When Others
Then
  --Set error logging variables
  l_errorNumber  := 56032;

  l_parameters   := 'i_status='   || i_status   || CHR(13) ||
                    'i_schoolList=' || i_schoolList;

  l_errorMessage := 'Error getting schools for "Operational" status'                          || CHR(13) || CHR(13) ||
                    'SQLERRM:'                             || CHR(13) || SQLERRM                             || CHR(13) || CHR(13) ||
                    'DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:' || CHR(13) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

  l_procedure    := 'POPULATE_SCHOOLS';

  l_owner        := 'USER';
          
  l_errorMessage := 'ERROR IN EXCEPTION'                          || CHR(13) || CHR(13) ||
                    'SQLERRM:'                             || CHR(13) || SQLERRM                || CHR(13) || CHR(13) ||
                    'DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:' || CHR(13) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
                        
  insert_log_entry(l_proc_name, l_parameters, l_errorMessage);
        --Log error in VODAERR table
        /*LOGGER.LOGERROR(ErrNum    => l_errorNumber, 
                        p_Param   => l_parameters,
                        p_ErrText => l_errorMessage,
                        p_PROC    => l_procedure,
                        p_OWNER   => l_owner);*/

End POPULATE_SCHOOLS;

I have checked the logs that I am logging in the code and can see that only the last index, in this case it is 10 in the example above, gets added to the apex collection. Suppose there are 3 or 4 rows returned in the second cursor, then it only adds the 3 or 4 rows in the last index of the first loop. It seems like it is overwriting the previous apex collection the whole time from the previous loop, because I can see that the index of the first loop gets incremented by looking at the logs. Does someone perhaps know why it is doing this and is this an oracle bug, because I tried using different kind of loops and nothing is working?

Without having your code and data it's challenging to figure out what is going wrong. All I can advise is: instrument your code by adding debug statements. You could also use the add_member function instead of procedure to check what is happening. Here is an example:

DECLARE
  l_collection VARCHAR2(100) := 'EMP';
  l_seq NUMBER;
  l_rows NUMBER;
BEGIN
  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => l_collection);
  apex_debug.info(
    p_message => 'KL Debug: before LOOP');

  FOR r IN (SELECT ename FROM emp)
  LOOP
    apex_debug.info(
        p_message => 'KL Debug: in LOOP ename: %0',
        p0        => r.ename);
    l_seq := APEX_COLLECTION.ADD_MEMBER (
              p_collection_name => l_collection,
              p_c001 => r.ename);
    apex_debug.info(
        p_message => 'KL Debug: in LOOP collection seq: %0',
        p0        => l_seq);
    SELECT count(*) 
      INTO  l_rows 
      FROM apex_collections 
      WHERE collection_name = l_collection; 
    apex_debug.info(
        p_message => 'KL Debug: in LOOP collection rowcount: %0',
        p0        => l_rows);
  END LOOP;
  apex_debug.info(
    p_message => 'KL Debug: after LOOP');
END;

Then run the page in debug, check the request for which the process is fired and go through the logs:

Hope you are doing well.

This problem was fixed.

The problem with this one was that it did not like the CHR(13) and CHR(10) when doing the loop. It seems like if you split using those two, it does not split correctly and instead just takes the last index if you use CHR(10) and the first index if you use CHR(13). To resolve this issue, I replaced both CHR(10) and CHR(13) with a pipe "|" and then proceeded to split them. Hope this makes sense.