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?
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.