Re: bulk binding

From: Lewis C <lewisc_at_excite.com>
Date: Mon, 16 May 2005 22:53:28 GMT
Message-ID: <709i81p0qelneciohka65i9e43v1dcmab9@4ax.com>

On 16 May 2005 11:16:47 -0700, "Eugene" <epipko_at_hotmail.com> wrote:

>Hi all,
>I have the following code:
>=========================================================
>PROCEDURE upc_reuse_gen_upc_list_p (vArray_size IN PLS_INTEGER DEFAULT
>100) IS
>
>TYPE sku_row IS TABLE OF sku%ROWTYPE;
>sku_rec sku_row;
>
>CURSOR upc_list IS
>select ...
>from upc a,
> sku b,
>where ......;
>
>BEGIN
> OPEN upc_list;
> LOOP
> FETCH upc_list BULK COLLECT INTO sku_rec LIMIT vArray_size;
>
> FORALL i IN 1..sku_rec.COUNT
> INSERT INTO upc_reuse_arch VALUES sku_rec(i);
>
> FORALL i IN 1..sku_rec.COUNT
> UPDATE upc
> SET upc_reuse_ind = '1',
> WHERE business_unit_id = sku_rec(i).business_unit_id
> AND upc_id = sku_rec(i).upc_id;
>
> EXIT WHEN upc_list%NOTFOUND;
>
> END LOOP;
> CLOSE upc_list;
>
> COMMIT;
>END upc_reuse_gen_upc_list_p;
>=========================================================
>Here is the question:
>Second FORALL fails with PLS-00436: implementation restriction: cannot
>reference fields of BULK In-BIND table of records.
>How can I make that update possible?
>
>Thanks,
>Eugene

Instead of a rowtype, create a nested table for each column in the table. Use those in you bulk collect into and then pick and chose the columns you want in your dml.

Lewis

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Mon May 16 2005 - 17:53:28 CDT