Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: question about bulk bind
I am afraid what the Metalink folks told you is correct. With 8i, you
can not use "FORALL" on a table of records. With 9i2, the problem is
resolved. You simply do something like:
forall i in x.first..x.last
insert into table_name values x(i);
where x is of table of record type. As you can see, you can now insert a whole record into a table in one shot - as has been dicussed recently here in a separate thread. AFAIK, with 8i, you have to create a collection of for each of the table columns you want to insert into.
rgaffuri_at_cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.0301131156.325577a1_at_posting.google.com>...
> I asked the following question on Metalink, and I was a bit troubled
> by my response. So Im looking for a second opinion.
>
> problem using bulk bind
>
> FORALL i in l_resourceID.FIRST.. l_resourceID.LAST
> INSERT INTO RD_ASSOC_CONTAINER_PATH
> VALUES (v_resource_id
> , CONST_RESOURCE_SEQ
> , v_curIndex -- step in the path
> , l_resourceID(i).PARENT_RESOURCE_ID
> , l_resourceID(i).PARENT_RESOURCE_SEQ
> , l_resourceID(i).CHILD_RESOURCE_ID
> , l_resourceID(i).CHILD_RESOURCE_SEQ);
>
> Compilation errors for PACKAGE BODY END_STATE33.PKGLEAFNODE
>
> Error: PLS-00435: DML statement without BULK In-BIND cannot be used
> inside FORALL
> Line: 160
> Text: INSERT INTO RD_ASSOC_CONTAINER_PATH
>
>
> WHat am I doing wrong?
>
> -------------------------------
> I was told that prior to 9i Release 2 you could only do a bulk bind on
> 1 column at a time. So If I used a 2 dimensional PL/SQL table, I would
> have to do 1 insert for each column???
>
> In my opinion this would kill the functionality of a bulk bind? This
> surprises me since Ive seen alot of references to its usefulness.
>
> 1. Is that correct?
> 2. If it is correct, what is the best work around?
> 3. If that answer is incorrect, what is the correct answer?
>
> Not sucking up, but the people on here are generally more
> knowleadgeble than the ones on Metalink...
Received on Mon Jan 13 2003 - 19:49:20 CST