Home » SQL & PL/SQL » SQL & PL/SQL » Stored procedure resultsets
Stored procedure resultsets [message #242216] Fri, 01 June 2007 02:01 Go to next message
lkennedy
Messages: 2
Registered: June 2007
Location: Sydney
Junior Member
Apologies if this is obvious, but its been a log time since I used oracle.

In SQL Server & Sybase (which I know best), if I want to return data from a stored procedure it is as simple as:

create procedure selProc as
select * from table
go


If I want to return multiple reult sets it is:

create procedure selProc as
select * from table
select * from table2
go

Last time I used oracle (about 7 years ago), you could only return results from a stored procedure using a cursor. Is this still the case ? If so could someone point me to a simple sample/manual page for this ?
If not, how to you do it in recent (9 and above) versions ?

Thanks,
Leigh.
Re: Stored procedure resultsets [message #242219 is a reply to message #242216] Fri, 01 June 2007 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use REF CURSOR.
For example:
SQL> var v1 refcursor
SQL> var v2 refcursor
SQL> create or replace procedure p (r1 out sys_refcursor, r2 out sys_refcursor)
  2  is
  3  begin
  4    open r1 for select ename from emp;
  5    open r2 for select dname from dept;
  6  end;
  7  /

Procedure created.

SQL> exec p(:v1,:v2);

PL/SQL procedure successfully completed.

SQL> print v1
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

SQL> print v2
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

4 rows selected.

SYS_REFCURSOR is a predefined REF CURSOR but you can defined your own.

Regards
Michel
Re: Stored procedure resultsets [message #242541 is a reply to message #242219] Sun, 03 June 2007 19:20 Go to previous messageGo to next message
lkennedy
Messages: 2
Registered: June 2007
Location: Sydney
Junior Member
Thanks.

It works, but it means you can't really abstract the internal away from the user as much - they need to know how many result sets are coming.

Can you provide me with an example of using the SYS_REFCURSOR ?
Re: Stored procedure resultsets [message #242552 is a reply to message #242541] Mon, 04 June 2007 01:02 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ In real world you always have to know how many variables (or result sets) you have to get, isn't it?

2/ SYS_REFCURSOR is like any other cursor. Just fetch it as the SQL*Plus print command does in my previous example.

You can also search for REF CURSOR in documentation, there are plenty of examples.

Regards
Michel
Previous Topic: Intermittent commit while inserting
Next Topic: Use of Index by tables
Goto Forum:
  


Current Time: Fri Dec 09 19:38:22 CST 2016

Total time taken to generate the page: 0.11001 seconds