Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql refcursor
do something like that.!!
CREATE OR REPLACE PACKAGE KSP_PLSQLRSETPKG
AS
TYPE RCT1 IS REF CURSOR;
END;
;
the first parameter "oraclebug" is to bypass the oracle bug doesn't accept a
string for the first parameters
when in the odbc tab sqlserver migration is on
CREATE OR REPLACE PROCEDURE KSP_TEST
( ORACLEBUG INT DEFAULT 0,
RC1 IN OUT KSP_PLSQLRSETPkg.RCT1,
RC2 IN OUT KSP_PLSQLRSETPkg.RCT1
)
AS
DYNSQL VARCHAR2(255);
BEGIN
begin
OPEN RC1 FOR SELECT 1 FROM DUAL;
end;
begin
OPEN RC2 FOR SELECT sysdate FROM DUAL;
end;
END ; after call this
exec ksp_test 0
the result:
1
1
1 Row(s) affected
SYSDATE
2002-06-18 09:33:15
1 Row(s) affected
"Ken Chesak" <kchesak_at_austin.rr.com> wrote in message
news:vbyP8.205661$9F5.11592609_at_typhoon.austin.rr.com...
> 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 Tue Jun 18 2002 - 08:47:52 CDT