Returning multiple values from Oracle Stored Procedure

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!
prgManAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
In oracle you should return ref cursor or associative array.
0
mrjoltcolaCommented:
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
schwertnerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark GeerlingsDatabase AdministratorCommented:
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
schwertnerCommented:
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
prgManAuthor Commented:
Thank you all for your help.  Sorry for the delay in replying.  

I'll learn this Oracle 'stuff' it kills me.  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.