Re: Returning rows from a stored procedure - how?

From: Thomas kyte <tkyte_at_us.oracle.com>
Date: 1997/03/29
Message-ID: <33406fa4.7232189@newshost>#1/1

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 Received on Sat Mar 29 1997 - 00:00:00 CST