Re: Stored Procedures that Return Result Sets

From: <sergey_s_at_my-deja.com>
Date: Wed, 08 Sep 1999 02:02:17 GMT
Message-ID: <7r4g32$hn1$1@nnrp1.deja.com>

Yes. You have to use REF CURSOR.

CREATE PACKAGE PKG_TEST AS
TYPE REFCUR_TYPE IS REF CURSOR;
END;
/

CREATE PROCEDURE SP_TEST (REFCUR_VAR IN OUT PKG_TEST.REFCUR_TYPE) AS BEGIN
OPEN REFCUR_VAR FOR SELECT * FROM SOME_TABLE; END;
/

You can also put the SP_TEST stored procedure inside the package for better modularity. You can call SP_TEST from other procedures like this

CREATE PROCEDURE SP_TRYIT AS
  LOCAL_REFCUR PKG_TEST.REFCUR_TYPE;
  MY_REC SOME_TABLE%ROWTYPE;
BEGIN
  SP_TEST(LOCAL_REFCUR);
  WHILE ...
  LOOP
    FETCH LOCAL_REFCUR INTO MY_REC;
    EXIT WHEN LOCAL_REFCUR%NOTFOUND;
    ...
  END LOOP;
END; You can also call such stored procs from outside Oracle environment. I know you can do it in Delphi (Windows), and, I'm sure a bunch of other languages that support this Oracle feature.

HTH
Sergey

In article <7r434q$vj7$1_at_madmax.keyway.net>, "Christian Carollo" <ccarollo_at_viva.com> wrote:
> Is it possible for Oracle 8i to return result sets for select queries?
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Sep 07 1999 - 21:02:17 CDT