Re: Bulk Inserting

Originally posted by Adam C
> 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

Unless I am mistaken, your code is attempting to do this (which doesn't need FORALL):

BEGIN
INSERT INTO subcriterion (CriterionID, SubCriterionNumber, SubCriterionName, SubCriterionDescription, SubCriterionCaveats, Active)
SELECT CriterionID, SubCriterionNumber, SubCriterionName, SubCriterionDescription, SubCriterionCaveats, Active FROM subcriterion;
dbms_output.PUT_LINE (SQL%ROWCOUNT);
END; i.e. it duplicates every row in the subcriterion table and then writes

     out how many rows were inserted. Is that what you want?

--
Posted via http://dbforums.com