PL/SQL 2 selects, 1 result set

I need to return 1 result set from 2 separate SQL statements. The results may be 1 row or many rows.
I am using an array to collect the data, then would like to use a refcursor as a return parameter. Oracle 8.1.7, thanks.

type refcur is ref cursor;
TYPE XYZ IS RECORD ( unit VARCHAR2(3), bjn number(10) ); TYPE the_array IS TABLE OF XYZ INDEX BY BINARY_INTEGER;

procedure d
( p_output out refcur )
is

	unit_ref refcur;
	bjn_ref refcur;
	output the_array;
	cnt number := 0;
	v_cnt number := 0;
	s_cnt number := 0;

begin
	for x in (
	select id_unit, max(sort_seq)
	from distribution_rule a
	where a.cd_type = 'UNIT'
	and a.is_active = 'Y'
	group by id_unit )
	loop
		cnt := cnt + 1;
 	output(cnt).unit := x.id_unit;   << first result
	end loop;

 s_cnt := cnt;
	cnt := 0;

	for x in (
	select id_bjn, max(sort_seq)
	from distribution_rule a
	where a.cd_type = 'CASE'
	and a.is_active = 'Y'
	group by id_bjn)
	loop
		cnt := cnt + 1;
    	output(cnt).bjn := x.id_bjn;  <<second result
	end loop;

open p_output for select 1 from dual; <<just to return something

 --select * from table(output); <<< move to refcursor ???

end;
end; Received on Fri Jun 14 2002 - 22:41:15 CDT