Re: How to get multiple row via a stored procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/10/17
Message-ID: <3449b15f.21586920@newshost>

With 7.2 and 7.3 of the database you have cursor variables. Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them). The cursor variables are opened with the privelegs of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. It uses the inputs to decide what database it will run a query on.

Here is a 7.2 / 7.3 example in SQL*Plus and PRO*C

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 } I've been told that the NEON ODBC adapter for Oracle supports cursor variables. Contact info can be found at http://internet1.neonsys.com/

Also, The beta version of Microsoft's latest Oracle ODBC driver (v2.0) is available at: http://www.microsoft.com/msdownload/rds/rdsdownload.htm. It is part of the RDS Server 1.5 Beta download. When you download it you have the option to install any of the included components (ADO, OLE DB, the Oracle ODBC driver, etc...).

Some of the features of the new driver are:   .....
- It can return a resultset from a stored procedure.   .....

On Thu, 16 Oct 1997 17:56:17 -0400, "Keith Primeaux" <Keith_Primeaux_at_email.msn.com> wrote:

>If you're using < Oracle 8 then this is not possible. I haven't used Oracle
>8 yet, but I thought I heard that
>they (Oracle) would start supporting resultsets returned from stored
>procedures. Maybe other Ora8 users
>could fill us in on this mystery.....?....?
>
>Anyway, the closest you could come to duplicating this functionality is
>using Views instead of Stored Procs.
>Access's Queries are hardly considered stored procs, so the Oracle Views
>should be able to handle any/all of
>the old logic and functionality. I think this will be the best you will be
>able to do......
>
>Good luck,
>KP
>
>
>Price Waterhouse LLP wrote in message
><01bcd813$0cf6a440$eb322299_at_nakhtar.us.pw.com>...
>>Hello,
>>
>>I am developing an application in VC++ and accessing the Oracle database
>>via ODBC. I have to access the data via stored procedure. I created a....
>
>
>>Is this possible at all in Oracle. I have to make my application compatible
>>with Access and Oracle. This method does work in Access. Although Access
>>stored procedure are Predefined queries.
>
>

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

NOTICE: In order to cut down on the amount of time I spend applying silly logic to email addresses like "In order to cut down on spam, remove THIS from my email" or "In order to cut down on spam, reverse my email address", etc, I will simply hit delete when that mail bounces back to me, which is what I do with spam myself.

I haven't figured out whats more time consuming for me, deleting spam or resending emails after re-reading the whole thing to figure out the algorithm to apply. Received on Fri Oct 17 1997 - 00:00:00 CDT