Re: Bulk Inserting

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Wed, 18 Jun 2003 18:58:28 -0700
Message-ID: <3EF118C4.3396214@exxesolutions.com>

Adam C wrote:

> andrewst <member14183_at_dbforums.com> wrote in message news:<3015186.1055928472_at_dbforums.com>...
> > 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?

> Hi Andrew

> Basically I have a series of tables
> parent/{child|parent}/{child|parent} where each child record is
> potentially a parent record for another related table. I am trying to
> create a procedure that will create duplicate entries of existing
> entries within tables and at the same time exchange old parent keys
> for new parent keys.

> Very nearly a cascade update scenario but not quite.

> Thanks very much for the input.

>
> Adam

Hope I'm posting to the correct thread. I lost the thread where someone wrote that you can not use BULK COLLECT and FORALL together and it is not true as demonstrated by the following:

CREATE OR REPLACE PROCEDURE fast_way IS

TYPE PartNum IS TABLE OF parent.part_num%TYPE INDEX BY BINARY_INTEGER;
x PartNum;

TYPE PartName IS TABLE OF parent.part_name%TYPE INDEX BY BINARY_INTEGER;
y PartName;

BEGIN
   SELECT part_num, part_name
   BULK COLLECT INTO x, y
   FROM parent;

   FORALL i IN x.FIRST .. x.LAST
   INSERT INTO child
   (part_num, part_name)
   VALUES
   (x(i), y(i));
   COMMIT;
END fast_way;
/

What you can't do is BULK COLLECT into objects.

--
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)

Received on Wed Jun 18 2003 - 20:58:28 CDT