Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Returning multiple values from Oracle Stored Procedure

Posted on 2009-07-14
6
Medium Priority
?
1,057 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 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

773 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