Bulk Inserting

From: Adam C <adam_at_ddisolutions.com.au>
Date: 18 Jun 2003 00:59:28 -0700
Message-ID: <8bdc35cd.0306172359.39711732@posting.google.com>

Hello All

I am struggling with a procedure that will do a batch/bulk insert of parent child data. I have been tinkering with the following (sorry about wrapping):

DECLARE

	type myt1 is table of subcriterion.CRITERIONID%type;
	type myt2 is table of subcriterion.SUBCRITERIONID%type;
	c myt1;
	sc myt2;
	newsc myt2;
BEGIN
 	 select subcriterionid, criterionid bulk collect into sc, c from

subcriterion;          

	 forall i IN sc.first..sc.last
	 		INSERT INTO subcriterion (CriterionID, SubCriterionNumber,

SubCriterionName, SubCriterionDescription, SubCriterionCaveats, Active)

                        SELECT c(i), SubCriterionNumber, SubCriterionName, SubCriterionDescription, SubCriterionCaveats, Active

			FROM subcriterion where SubCriterionID = sc(i)
			RETURNING SubCriterionID BULK COLLECT INTO newsc;
			 
 	 dbms_output.PUT_LINE (newsc.count);

END; I am getting an error saying "SQL command not properly ended". From what I can
tell this is because I have tried to use the SELECT statement in the FORALL
block (read somewhere that this isn't allowed I think).

Can anyone suggest a fix or better approach to this situation?

(Platform Win2k and Oracle8i)

Thanks

Adam C Received on Wed Jun 18 2003 - 02:59:28 CDT