0 Replies - 703 Views - Last Post: 20 June 2015 - 04:23 AM

#1 MarmiteX1  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 160
  • Joined: 17-October 09

Bulk insert multiple collections

Posted 20 June 2015 - 04:23 AM

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:

Table Audit_table
  • Id
  • Student_Id
  • StudentBioId


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 Student ID:
CREATE OR REPLACE TYPE studentIDs IS VARRAY (256) OF NUMBER;
CREATE OR REPLACE TYPE studentBio IS VARRAY (256) OF NUMBER;

BEGIN
   FORALL i IN 1..studentIDs.COUNT
      INSERT INTO audit_table(Id, Student_id, StudentBioId) 
      VALUES  (-1, studentIDS(i), null);
END;





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.

Kind Regards,

This post has been edited by MarmiteX1: 20 June 2015 - 04:23 AM


Is This A Good Question/Topic? 0
  • +

Page 1 of 1