pl/sql refcursor

I need to return 1 result set from 2 separate SQL statements. I am using an array to collect the data, then would like to use a refcursor as a return parameter. Once I have the data in the array is there a way to put it
in a refcursor? How do you count the number of rows in a refcursor? I am using
the array so I can tell how many rows are returned. Oracle 8.1.7, thanks.

This sql has the 2 selects and collects the information in an array.

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 Mon Jun 17 2002 - 22:56:11 CDT