Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: pl/sql refcursor

Re: pl/sql refcursor

From: Sylvain Dumont <sylvain_dumont_at_karat.com>
Date: Tue, 18 Jun 2002 09:47:52 -0400
Message-ID: <pSGP8.822$SR3.318510@localhost>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US