Re: PL/SQL 2 selects, 1 result set

Ken Chesak wrote:

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

Do you have a question?

Daniel Morgan Received on Mon Jun 17 2002 - 02:13:20 CDT