Solved

Returning multiple values from Oracle Stored Procedure

Posted on 2009-07-14
6
1,026 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
6 Comments
 
LVL 47

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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now