Re: FORALL and BULK COLLECT INTO together in SELECT statements

From: JTommaney <cdos_jtommaney_at_comcast.net>
Date: 17 Oct 2005 11:33:50 -0700
Message-ID: <1129574030.552564.183620@g49g2000cwa.googlegroups.com>

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(); Received on Mon Oct 17 2005 - 13:33:50 CDT