Re: what pl/sql construct can return multiple rows?

From: Madhusudhanan Sampath <madhulist_at_hotmail.com>
Date: Tue, 19 Mar 2002 09:18:38 -0800
Message-ID: <F001.0042D54F.20020319091838@fatcity.com>

Lots of examples avlbl from Concepts manual; also pl check asktom.com

Step 1) Declare a ref cursor inside a package

CREATE PACKAGE APACK AS
   TYPE RefCurTyp IS REF CURSOR;
END APACK; Step 2) Employ the ref curosr IN OUT variable inside the procedure..note that the cursor is opened but not fetched..

PROCEDURE demo_ref (

      refcurvar IN OUT RefCurTyp,
      choice     NUMBER) IS
   BEGIN
      IF choice = 1 THEN
         OPEN refcurvar FOR SELECT * FROM emp;
      ELSIF choice = 2 THEN
         OPEN refcurvar FOR SELECT * FROM dept;
      ELSIF choice = 3 THEN
         OPEN refcurvar FOR SELECT * FROM sal;
      END IF;

END demo_ref;

>From: "Igor Neyman" <ineyman_at_perceptron.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: what pl/sql construct can return multiple rows?
>Date: Tue, 19 Mar 2002 08:08:35 -0800
>
>Use Ref Cursor (reference cursor) as a parameter in stored procedure.
>Sorry, don't have handy sample code, but you can lookup one in any PL/SQL
>book or docs.
>
>Igor Neyman, OCP DBA
>ineyman_at_perceptron.com
>
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Tuesday, March 19, 2002 8:58 AM
>
>
> > Hi,
> >
> > Is there a way to write a procedure to return multiple rows? I have
>some
> > nasty SQL that I'd like to convert to run server-side, but how do you
>spit
> > out multiple rows from PL/SQL?
> >
> > thx
> >
> >
> > Bill Magaliff
> > Framework, Inc.
> > 914-631-2322
> >

---

MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Madhusudhanan Sampath
  INET: madhulist_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Mar 19 2002 - 11:18:38 CST