Re: Bulk Inserting

Adam C wrote:

> 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

Dump RETURNING and it should work. And I'd dump the DBMS_OUTPUT too as the answer to the question is self-evident from the number of records fetched.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)