Solved

Returning multiple values from Oracle Stored Procedure

Posted on 2009-07-14
6
1,042 Views
Last Modified: 2012-05-07
Hi experts,
I have created a stored procedure that is pulling multiple counts, I want to return 5 value counts.  In SQL Server you can just run something like:
    SELECT cntA, cntB, cntC

But is there a way to do this in Oracle?

thanks in advance!
0
Comment
Question by:prgMan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 360 total points
ID: 24851275
In oracle you should return ref cursor or associative array.
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 120 total points
ID: 24851356
Return multiple show? Multiple columns? Multiple rows?

You can return multiple values with OUT parameters, but if it varies in the number, you need to do as schwertner suggests, use a ref cursor.

-- ref cursor with SYS_REFCURSOR
--
CREATE TABLE T(id integer);
insert into t values(1);
insert into t values(2);
insert into t values(3);
 
CREATE OR REPLACE FUNCTION getall RETURN SYS_REFCURSOR
IS
  result SYS_REFCURSOR;
BEGIN
  open result for select * from t;
  return result;
END;
/

Open in new window

0
 
LVL 48

Accepted Solution

by:
schwertner earned 360 total points
ID: 24851385
This is an example:

create or replace function refcursor_dept return sys_refcursor as
TYPE v_cursor is REF CURSOR;
p_cursor v_cursor;
begin
open p_cursor for
select * from dept;
return p_cursor;
end;

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 20 total points
ID: 24851921
The Oracle client tools (Oracle Forms and Oracle Reports) build their own record groups internally without depending on PL\SQL, so the simplest PL\SQL procedures in Oracle do not support returning result sets or arrays.  If you want that functionality from Oracle, you have to use a "ref cursor" as others have already mentioned.

Of course, Oracle will allow your application to just execute a query directly if you want.  Then you don't even need a stored procedure!  But whether this is possible for you or not, or a good idea or not, will depend on your application and your security model.

If you are familiar with SQL Server, but new to Oracle, this is just one of many differences you will have to get used to.  Be aware that the way programs are usually written for SQL Server (especially whether "temp" tables are used or not) is very different from the best way to get the job done with Oracle.
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 360 total points
ID: 24852053
Another way is to return only one long string value with some delimiter (like comma between the values) that can be using a technology known as pipelined table read via select statement in the calling PL/SQL subroutine, block, etc.
0
 

Author Closing Comment

by:prgMan
ID: 31603327
Thank you all for your help.  Sorry for the delay in replying.  

I'll learn this Oracle 'stuff' it kills me.  
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question