Re: Procedure to return a result set

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/12
Message-ID: <337832ba.5402858@newshost>#1/1

On 8 May 1997 20:50:08 GMT, "Bob Weber" <bweber_at_onramp.net> wrote:

>Ok! I give up!!
>It's very simple under MS SQL to write a stored procedure which returns a
>result set. Unfortunately I can't seem to figure out how to do the same
>thing under ORACLE!!!
>
>Could anyone help? let's say I want a procedure to
>SELECT PRICE FROM PRICE_TABLE WHERE VENDOR = :VEND
>how can i create a procedure to do this and how can i successfully execute
>ie. EXECUTE SP_PRICE( "ORACLE");
>and then get back
>PRICE
>----------
>1.00
>2.00
>3.45
>etc...
>
>Sorry to seem so dense, but I seem to be missing something very basic!
>Thanks,
>Bob Weber
>bweber_at_onramp.net

The following was done in 7.3 of the database. This capability started with 7.2 of the database. The syntax would be a little different for 7.2 (let me know if you need that one). You need to create one types package (i have one in my database where I store lots of common types like record defs and array types for applications). In it, you type a cursor reference type.

create or replace package types
as

    type curs is ref cursor;
end types;
/

Then you create a procedure that takes that type as an in/out parameter and any other inputs you want. You could use the inputs to open the query as bind variables although in this example I am opening a different query based on the inputs.    

create or replace procedure show_cursor( p_cursor in out types.curs,

                                         p_input  in     varchar2 default NULL)

as
begin

    if ( upper( p_input ) = 'DEPT' ) then

        open p_cursor for select * from dept;     else

        open p_cursor for select * from emp;     end if;
end;
/

Then, to get this to work in SQL*Plus, you declare a variable C to hold the cursor, call the stored procedure to get the cursor, and print it as such:  

variable C refcursor
exec show_cursor( p_cursor => :C, p_input => 'dept' ) print C
exec show_cursor( p_cursor => :C )
print C

In Pro*C this might look something like:

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 EXEC SQL ALLOCATE :my_cursor;
REM

REM 	EXEC SQL EXECUTE BEGIN empCurType.open_cursor( :my_cursor, 'EMP' ); 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 Mon May 12 1997 - 00:00:00 CDT