Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Returning multiple values from Oracle Stored Procedure

Posted on 2009-07-14
6
Medium Priority
?
1,049 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 1440 total points
ID: 24851275
In oracle you should return ref cursor or associative array.
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 480 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 1440 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 80 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 1440 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 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