Re: Bulk Inserting

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 18 Jun 2003 11:24:35 -0700
Message-ID: <130ba93a.0306181024.f14f826@posting.google.com>

You CAN use select within the FORALL statement. But you can not use FORALL and BULK COLLECT together in a select statement.

SQL> select * from a1;

C1 C2 C3
-- ---------- ------

G         110 G_tag
O         120 O_tag
Q         130 Q_tag
a         140 a_tag
b         150 b_tag
c         160 c_tag
d         170 d_tag
e         180 e_tag
f         190 f_tag
g         200 g_tag
h         210 h_tag

11 rows selected.

SQL> -- select within FORALL
SQL> declare

  2  type c1_type is table of A2.c1%type;
  3  type c2_type is table of A2.c2%type;
  4  type c3_type is table of A1.c3%type;
  5  x c1_type;

  6 y c2_type;
  7 w c3_type;
  8 begin
  9 select c1,c2 bulk collect into x,y from A2;  10 FORALL i in 1..x.count
 11 update A1 set c2=(select c2 from a3 where c1=x(i)) where c1=x(i)
 12 RETURNING c3 BULK COLLECT INTO w;  13
 14 For i in 1..w.count loop
 15 DBMS_OUTPUT.PUT_LINE(w(i));
 16 end loop;
 17
 18 end;
 19 /

G_tag
O_tag
Q_tag
a_tag
b_tag
c_tag
d_tag
e_tag
f_tag
g_tag
h_tag

PL/SQL procedure successfully completed.

SQL> select * from a1;

C1 C2 C3
-- ---------- ------

G         111 G_tag
O         121 O_tag
Q         131 Q_tag
a         141 a_tag
b         151 b_tag
c         161 c_tag
d         171 d_tag
e         181 e_tag
f         191 f_tag
g         201 g_tag
h         211 h_tag

11 rows selected.

SQL> rollback;

Rollback complete.

It is not clear what you wish to do. Avoid BULK COLLECT within FORALL and you will be fine.

• Jusung Yang

adam_at_ddisolutions.com.au (Adam C) wrote in message news:<8bdc35cd.0306172359.39711732_at_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 - 13:24:35 CDT