I am developing an application where i would like to write a log data that has been requested.
Currently i load student data based the student ids i have passed into my procedure.
I then attempt to load sub data for the student based on the request e.g Skills, Hobbies, Languages, Grades, etc etc. These are passed in my stored proc as individual collections.
After everything has been loaded for each student I have to log their student id and the corresponding static sub data identifier regarding the sub data e.g Skills has an id of 45, Hobbies has an id of 46 etc etc. The static sub identifier is the same for all students.
The table can have multiple records for that student(based on whats requested and providing it exists), the audit table looks like this:
I would like some advice on the code i am trying to write. I am passing in two varrays, firstly the student identifiers and secondly identifiers relating to the sub data that has been requested for that student.
What is the convention for looping through multiple collections and inserting them appropriately against that student?
Currently i can only get one of them to work which is the StudentId varray:
CREATE OR REPLACE TYPE studentIDs IS VARRAY (256) OF NUMBER;
CREATE OR REPLACE TYPE studentBio IS VARRAY (256) OF NUMBER;
FORALL i IN 1..studentIDs.COUNT
INSERT INTO audit_table(Id, Student_id, StudentBioId)
VALUES (-1, studentIDS(i), null);
I would like to populate the StudentBioId for that student as well but i am unsure of how to do this appropriately for each student.
Advice/Examples would be much appreciated.