Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: bulk binding
Eugene 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
Do as Michel suggests. If is extremely fast and efficient.
-- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)Received on Mon May 16 2005 - 19:26:13 CDT