Re: Getting a Stored Procedure to Return a Recordset?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/10
Message-ID: <8hu40l$66s$1@nnrp1.deja.com>#1/1

In article <394269B9.A72FDCD1_at_gate.net>,   "Keith L. Norris" <kln_at_gate.net> wrote:
> How can I get a stored procedure to return a recordset. What I am
> trying to do is call a stored procedure from an MS Access97 program
 via
> a pass-through query which supplies parameters to the stored
 procedure.
> I want the stored procedure to get some records and then weed them out
> and do some manipulation and then send the results of that which will
 be
> several rows of data in a structure that I think of as a temporary
> non-persistent table back to the pass-through query so a report can
 then
> have its recordsource set to it? Can this sort of thing be done? How
> would it be done? I want the back-end to do all the manipulation
> because as I understand that will be alot faster. I do not want the
> resultset that is passed back to the calling program to be persistent.
>
> Thank you very much!!!
> Keith
>
>

result sets are return from stored procedures via ref cursors see: http://osi.oracle.com/~tkyte/ResultSets/index.html for info on that part of the process with various examples...

we can do this in 8.0 and up (the process you describe above - send inputs to an SP, let the SP do some work and filter the results and return what it wants to)....

It could look like this:

scott_at_8i> create or replace type myScalarType as object

  2  (   x     int,
  3      y    date,
  4      z    varchar2(25)

  5 )
  6 /

Type created.

scott_at_8i>
scott_at_8i>
scott_at_8i> create or replace type myTableType as table of myScalarType;

  2 /

Type created.

scott_at_8i>
scott_at_8i>
scott_at_8i> create or replace package result_set_pkg
  2  as
  3      type rc is ref cursor;
  4
  5      procedure demo_proc( p_start_row in number,
  6                           p_end_row in number,
  7                           p_cursor in out rc );

  8 end;
  9 /

Package created.

scott_at_8i>
scott_at_8i> create or replace package body result_set_pkg   2 as
  3

  4  procedure demo_proc( p_start_row in number,
  5                       p_end_row in number,
  6                       p_cursor in out rc )
  7  as
  8      l_data             myTableType := myTableType();
  9      l_cnt              number default 0;
 10  begin
 11      for x in ( select * from emp order by sal desc )
 12      loop
 13          l_cnt := l_cnt + 1;
 14          if ( l_cnt >= p_start_row )
 15          then
 16              l_data.extend;
 17              l_data(l_data.count) :=
 18                          myScalarType( x.empno, x.hiredate,
x.ename );
 19          end if;
 20          exit when l_cnt = p_end_row;
 21      end loop;
 22
 23      open p_cursor for
 24          select *
 25            from the ( select cast( l_data as mytableType )
 26                         from dual );

 27 end;
 28
 29 end;
 30 /

Package body created.

scott_at_8i>
scott_at_8i> set autoprint on
scott_at_8i> variable x refcursor
scott_at_8i> exec result_set_pkg.demo_proc( 5, 10, :x )

PL/SQL procedure successfully completed.

         X Y Z

---------- --------- -------------------------
      7698 01-MAY-81 BLAKE
      7782 09-JUN-81 CLARK
      7499 20-FEB-81 ALLEN
      7844 08-SEP-81 TURNER
      7934 23-JAN-82 MILLER
      7521 22-FEB-81 WARD

6 rows selected.

Actually, in this particular case, we can even skip the ref cursor if we wanted to and just select * from (PLSQL function) like this:

scott_at_8i> create or replace function
  2 demo_proc2( p_start_row in number,
  3 p_end_row in number ) return myTableType   4 as

  5      l_data             myTableType := myTableType();
  6      l_cnt              number default 0;
  7  begin
  8      for x in ( select * from emp order by sal desc )
  9      loop
 10          l_cnt := l_cnt + 1;
 11          if ( l_cnt >= p_start_row )
 12          then
 13              l_data.extend;
 14              l_data(l_data.count) :=
 15                    myScalarType( x.empno, x.hiredate, x.ename );
 16          end if;
 17          exit when l_cnt = p_end_row;
 18      end loop;
 19          return l_data;

 20 end;
 21 /

Function created.

scott_at_8i>
scott_at_8i> select *
  2 from the ( select cast( demo_proc2(5,10) as mytableType )

  3                 from dual );

         X Y         Z
---------- --------- -------------------------
      7698 01-MAY-81 BLAKE
      7782 09-JUN-81 CLARK
      7499 20-FEB-81 ALLEN
      7844 08-SEP-81 TURNER
      7934 23-JAN-82 MILLER
      7521 22-FEB-81 WARD

6 rows selected.

So you don't necessarily need to call a stored procedure and get a result set -- you can just select * from the stored procedure... (still need the object types though)

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sat Jun 10 2000 - 00:00:00 CDT