Re: Help! How can I return a result set from a stored procedure.

From: Christopher Jones <cjones_at_au.oracle.com>
Date: 1995/12/30
Message-ID: <x7spi3jxoh.fsf_at_hurrah.au.oracle.com>#1/1

In article <30E40D3D.20F0_at_osf1.gmu.edu> Maruf Ahmed <muppa1_at_osf1.gmu.edu> writes:

> I would really appreciate help in figuring out how I can return
> a result set from a stored procedure.

Use a REF CURSOR variable (introduced PL/SQL 2.2, Oracle7 7.2) in the stored procedure, and fetch from the cursor in your client program. SQL*Plus 3.2 will do the fetching for you:

   SQL> create or replace package name_pck is

     2    type ednames is record (ename varchar2(10), dname varchar2(14));
     3    type ecurtype is ref cursor return ednames;
     4    procedure get_ednames (maxdeptno in number, a in out ecurtype);
     5  end;
     6  /

   Package created.

   SQL>
   SQL> create or replace package body name_pck is

     2    procedure get_ednames (maxdeptno in number, a in out ecurtype) as
     3    begin
     4        open a for
     5          select   ename, dname
     6          from     emp, dept
     7          where    emp.deptno = dept.deptno
     8          and      emp.deptno = maxdeptno
     9          order by ename;

    10 end;
    11 end;
    12 /

   Package body created.

   SQL>
   SQL> set autoprint on
   SQL>
   SQL> variable b refcursor;
   SQL>
   SQL> column ename heading Name
   SQL> column dname heading Department
   SQL>
   SQL> execute name_pck.get_ednames(30, :b)

   PL/SQL procedure successfully completed.

   Name Department

• -------------- ALLEN SALES BLAKE SALES JAMES SALES MARTIN SALES TURNER SALES WARD SALES

Here the SQL*Plus REFCURSOR bind variable 'b' is bound to a REF CURSOR type 'ecurtype'. When get_ednames is executed, 'b' is instantiated to be a cursor for the SELECT statement in it. SQL*Plus fetches records using this cursor.

Chris

-- 
Christopher Jones, cjones_at_au.oracle.com

Received on Sat Dec 30 1995 - 00:00:00 CET