Re: Bulk Inserting

Hi Adam,

   The preoblem here lies in /* RETURNING SubCriterionID BULK COLLECT INTO newsc;*/ returning isnt a keyword i think, and am dont think u can do return from select here.

Remove the clause and the query will work, i think. U already have the values that you are inserting in sc.

Abhijith.

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
Received on Wed Jun 18 2003 - 03:59:01 CDT