Re: FORALL and BULK COLLECT INTO together in SELECT statements

psrao79_at_yahoo.com wrote:
> I have a procedure implementation below.
>
> Please suggest an efficient WORK AROUND.
>
>
>
>
> CREATE OR REPLACE TYPE DnisNumberList IS TABLE OF CHAR(11);
> /
>
> CREATE OR REPLACE TYPE DateList IS TABLE OF DATE;
> /
>
> CREATE OR REPLACE TYPE NumberList IS TABLE OF NUMBER;
> /
>
> PROCEDURE replace_dnis_list (
> user_name IN user_account.user_name%TYPE,
> dnis_number_list IN DnisNumberList,
> start_time_list IN DateList,
> end_time_list IN DateList,
> dnis_info_pk_list OUT NumberList )
> AS
> BEGIN
>
> FORALL i IN 1..dnis_number_list.COUNT
> SELECT dnis_info_pk
> BULK COLLECT INTO dnis_info_pk_list
> FROM dnis_info
> WHERE
> dnis_number = dnis_number_list(i) AND
> start_time = start_time_list (i) AND
> end_time = end_time_list(i);
>
> END;
> /
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 102/3 PLS-00432: implementation restriction: cannot
> use FORALL and BULK
> COLLECT INTO together in SELECT statements

www.psoug.org
click on Morgan's Library
click on Bulk Binding

There are many different working examples.

One caution ... you the LIMIT clause in your BULK COLLECTION and tune the size of the array for optimum performance.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)