Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: question about bulk bind

Re: question about bulk bind

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 13 Jan 2003 17:49:20 -0800
Message-ID: <130ba93a.0301131749.5fc49ec8@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US