Re: Returning rows from a stored procedure - how?

From: Thomas kyte <tkyte_at_us.oracle.com>
Date: 1997/04/01
Message-ID: <33422722.866455@newshost>#1/1

On Tue, 01 Apr 1997 01:57:58 GMT, lsantos_at_pobox.com (Luis Santos) wrote:

>Andy Noble <andy_at_dataworkshop.demon.co.uk> wrote:
>
>> However I cannot find ANY documentation telling me how to
>> return a result set from a SELECT statement back to the
>> front end application. I can only find comments that it is
>> possible.
>
> You haven't found any documentation about result sets in
>functions or storage procedures because IT'S NOT POSSIBLE!
>
> I think that Oracle8 may be capable of doing this (as
>Microsoft SQL Server is...)
>
> There's some ways (no one easy or transparent) to do this. You
>can use a pipe in the storage procedure to send data to application
>program, before the procedure returns. You can write the result set to
>some temporary table and allows the application program to select from
>it and so on...
>
>
> Regards,
>--
>Luis Santos
>Relacional Consultoria
>Rio de Janeiro - Brazil

Starting with 7.2 of the database, there is an easier way to accomplish this called cursor variables. Here is a SQL*Plus and PRO*C example. The procedure open_cursor will return a result set to the client application. The result set it gets will depend on the inputs to the procedure.

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 Tue Apr 01 1997 - 00:00:00 CST