Re: Returning rows from a stored procedure

From: Oren Nakdimon <oren_at_gsit.co.il>
Date: 1997/04/03
Message-ID: <33436CA0.7B96@gsit.co.il>#1/1

Hi.

Thomas kyte wrote:

On 26 Mar 1997 00:31:10 GMT, stevec_at_zimmer.csufresno.edu (Steve Cosner) wrote:

>In article <Ey6a8BAa8QMzEwWG_at_dataworkshop.demon.co.uk>,
>Andy Noble <andy_at_dataworkshop.demon.co.uk> wrote:
>>We are using Oracle Workgroup Server on an NT box,
>>and developing a front end application in VB v4 16-bit
>>(no Remote Data Objects (RDO) available).
>>

[snip]

>>I would be grateful if someone could supply me with an example
>>of a stored procedure that returns rows to my front end app.
>
>You do it in a package procedure, which then opens a cursor, and on

>repeated calls, returns multiple rows. Your package needs to do a
>minimum of 3 things, (which means 3 different procedures): Open
the
>cursor, fetch and return rows, and close the cursor.
>

[perfectly valid example snipped]

>
>Regards,
>Steve Cosner

Starting with 7.2 of the database, there is an easier way to accomplish this called cursor variables. I don't know if VB supports
their use or not yet. Here is a SQL*Plus and PRO*C example tho:

create or replace package empCurType
as

cursor c1 is select ename, mgr, dept.deptno, dname, loc from emp,
dept;

type empCur is ref cursor return c1%rowtype;

          procedure open_cursor( p_cursor in out empCur,
                                                     p_deptno in

  number );
  end;
  /
  show errors

create or replace package body empCurType as

procedure open_cursor( p_cursor in out empcur, p_deptno in number ) is
begin

          if ( p_deptno in ( 10, 20 ) ) then      -- open local query
                  open p_cursor for
                          select ename, mgr, dept.deptno, dname, loc
                        from emp, dept
                           where emp.deptno = dept.deptno and

dept.deptno =
p_deptno;

          else
                  open p_cursor for
                          select ename, mgr, dept.deptno, dname, loc
                        from emp_at_aria.world, dept_at_aria.world
                           where emp.deptno = dept.deptno and

dept.deptno =
p_deptno;

end if;
end open_cursor;

  end empCurType;
  /
  show errors

REM --------------- SQL*Plus using RefCursor to display results

variable C refcursor
exec empCurType.open_cursor( :C, 10 )

print C

  REM ----------- PRO*C  Getting the cursor ------------------

  REM
  REM And then the pro*c would look like 'normal' pro*c code except   that
  the EXEC
  REM SQL OPEN Cursor statement would now be a pl/sql call as follows:

  REM
  REM
  REM static void process()
  REM {
  REM EXEC SQL BEGIN DECLARE SECTION;

  REM     typedef char asciiz;
  REM     EXEC SQL TYPE asciiz IS STRING(100);
  REM     SQL_CURSOR      my_cursor;
  REM     asciiz          ename[40];
  REM     int                     mgr;
  REM     short           mgr_i;
  REM     int                     deptno;
  REM     asciiz          dname[50];
  REM     asciiz          loc[50];
  REM     int                     i;

  REM EXEC SQL END DECLARE SECTION;
  REM
  REM EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();   REM
  REM
  REM EXEC SQL ALLOCATE :my_cursor;
  REM
  REM EXEC SQL EXECUTE BEGIN empCurType.open_cursor( :my_cursor,   10
  ); end;
  REM END-EXEC;
  REM

  REM     for( ;; )
  REM     {
  REM             EXEC SQL WHENEVER NOTFOUND DO BREAK;
  REM             EXEC SQL FETCH :my_cursor
  REM                     INTO :ename, :mgr:mgr_i, :deptno, :dname,

:loc;
REM

  REM             printf( "%s, %d, %d, %s, %s\n",
  REM                             ename, mgr_i?-1:mgr, deptno, dname,
  loc );
  REM     }
  REM     EXEC SQL CLOSE :my_cursor;

  REM }   Thomas Kyte
  tkyte_at_us.oracle.com
  Oracle Government
  Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Does anyone know how to implement it in JDBC ?

Oren Nakdimon
Golden Screens Ltd.
email: oren_at_gsit.co.il Received on Thu Apr 03 1997 - 00:00:00 CST

Re: Returning rows from a stored procedure - how?