FORALL and BULK COLLECT INTO together in SELECT statements

[p***@yahoo.com]

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

[JTommaney]

You need to think of the Forall construct as an SQL statement rather
than a PL/SQL looping construct as it looks like you are doing here. I
do have an example of Bulk Collect and then Forall as an example of
that syntax, however I don't see a solution to what you are trying to
accomplish here.

Can you provide your Oracle version and a description of what you are
trying to do here?


You can create a record type against a cursor declaration and then use
Fetch .. Bulk Collect into and then Forall .. Insert.

I have included the save exceptions clause but not all supporting
declarations for that.


---------------------------------------------------
-- CURSOR DECLARATION ----------------------------
---------------------------------------------------
cursor cur_amm_for_insert is
SELECT
'*' amm_recovery,
m.entity_code amm_club_num,
. . .
FROM ( /*bpe_flags*/ select bpe.billing_profile_id,
. . .

-------------------------------------
-- declare record type for the cursor
--------------------------------------
type cur_amm_for_insert_collection is table of
cur_amm_for_insert%rowtype;

--------------------------------------
-- declare local variable of the record type
--------------------------------------
l_amm_coll cur_amm_for_insert_collection;

--------------------------------------------------
-- BEGIN -----------------------------------------
--------------------------------------------------
begin
OPEN cur_amm_for_insert;
LOOP
FETCH cur_amm_for_insert BULK COLLECT INTO l_amm_coll
LIMIT 100000;
BEGIN
FORALL indx IN 1..l_amm_coll.COUNT SAVE EXCEPTIONS
INSERT INTO jt_merc_ar_member_master_load VALUES
l_amm_coll(indx);
EXCEPTION
WHEN bulk_errors THEN
-------------------------------------------
-- HANDLE EACH INSERT EXCEPTION -----------
-------------------------------------------

[p***@yahoo.com]

Hi,

My requirement is this:

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 );

I have three nested tables sent to me as parameters. They also form
the selection criteria in the WHERE condition.

That is, dnis_number_list(i), start_time_list(i) and end_time_list(i)
uniquely identify one row in dni_info table.

Now, I want to select the primary keys of those rows and send it to
another procedure once again as a Nested Table to be stored in an
actual Oracle table.

I am using Oracle 8.1.7

Thanks

[Jack Addington]

I don't know if it is supported in 8.1.7 but in 9.2 I can write this as

SELECT dnis_info_pk
BULK COLLECT INTO dnis_info_pk_list
FROM dnis_info
WHERE
dnis_number IN (select column_value from table(cast(dnis_number_list as
DnisNumberList)) AND
start_time IN (select column_value from table(cast(start_time_list as
DateList)) AND
end_time IN (select column_value from table(cast(end_time_list as
DateList));

Post by p***@yahoo.com
Hi,
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 );
I have three nested tables sent to me as parameters. They also form
the selection criteria in the WHERE condition.
That is, dnis_number_list(i), start_time_list(i) and end_time_list(i)
uniquely identify one row in dni_info table.
Now, I want to select the primary keys of those rows and send it to
another procedure once again as a Nested Table to be stored in an
actual Oracle table.
I am using Oracle 8.1.7
Thanks

[JTommaney]

Jack's syntax works fine in 8.1.7, but you will want to correlate the
different column_values from the multiple in clauses. You can use the
in-line view stuff available to correlate the table variables as shown
below.

Some other options I would encourage you to consider:
1.) If not prevented by other business logic, it may may sense to
insert directly into the table rather than passing these values around
like this. Single statement SQL will out-perform PL/SQL. Can this be
done without using PL/SQL?

2.) There may be an alternate solution using a table of a record type
that wouldn't require the in-line views as used here, but may not fit
the rest of the architecture you are building.

Test before using, please:

--drop table dnis_info;
create table dnis_info
( dnis_info_pk number(9),
dnis_number CHAR(11),
start_time date,
end_time date);


insert into dnis_info select rownum * 10,
rownum,
trunc(sysdate - 1),
trunc(sysdate -2)
from all_objects where rownum <= 10;
commit;



create or replace PROCEDURE replace_dnis_list
as
l_dnis_number_list DnisNumberList;
l_start_time_list DateList;
l_end_time_list DateList;
l_dnis_info_pk_list NumberList;
type l_cur_type IS REF CURSOR;
l_cur l_cur_type;
begin
l_dnis_number_list := DnisNumberList( '2','3','4','5');
l_start_time_list := DateList( '10-16-2005' ,'10-16-2005'
,'10-16-2005' ,'10-12-2005' );
l_end_time_list := DateList( '10-15-2005' ,'10-15-2005'
,'10-15-2005' ,'10-09-2005' );
open l_cur for select dnis_info_pk
from dnis_info
where ( dnis_number, start_time, end_time) in
( select DNL.column_value, STL.column_value, ETL.column_value
from
( select rownum cnt , column_value from (
select column_value from table(cast(l_dnis_number_list as
DnisNumberList ))
)) DNL,
( select rownum cnt , column_value from (
select column_value from table(cast(l_start_time_list as
DateList ))
)) STL,
( select rownum cnt , column_value from (
select column_value from table(cast(l_end_time_list as
DateList ))
)) ETL
where dnl.cnt = stl.cnt and dnl.cnt = etl.cnt
);
fetch l_cur bulk collect into l_dnis_info_pk_list;
close l_cur;
for x in 1 .. l_dnis_info_pk_list.count loop
dbms_output.put_line('l_dnis_info_pk_list - ' ||
l_dnis_info_pk_list(x) );
end loop;
end;
/

show errors
exec replace_dnis_list();

[DA Morgan]

Post by p***@yahoo.com
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.

[p***@yahoo.com]

Thanks JTommaney , it works efficiently.