Result Sets from Oracle Stored Procedures.
Balaji T, June 22, 2002 - 12:20 am UTC
Thank you for the clarification.
The example given narrates how to return result sets using functions.
But the stored procedures we are working on have result sets as well as a return statement with expression. Functions cannot anyway return more than one value (Result Set + Return Value).
Can we have a method to handle this in Stored Procedures (instead of functions)?
June 22, 2002 - 9:31 am UTC
create or replace function f( x in number,
result1 in OUT types.rc,
result2 in OUT types.rc ) return number
there is a function that takes as INPUT a number X, returns two result sets result1 and result2 and finally returns a number as a value.
Functions, like procedures, accept IN, IN OUT, or OUT parameters. A function is nothing more then a procedure that returns a values.
A stored procedure would look like:
create or replace procedure p( result in out types.rc )
and when you called it, instead of executing:
begin :x := f(); end;
you would:
begin p(:x); end;
is all -- they both can do this as handily as the next. Also, read the PLSQL documentation, it has alot to say about ref cursors.
Returning Result Sets through Oracle
Balaji T, June 24, 2002 - 2:18 am UTC
Yes. The suggestion was very very useful and we are able to solve the problems now.
more on returing results from Stored Procedures
Phillip Miller, July 06, 2005 - 12:58 pm UTC
The information in this article is excellent. Thank you. I'm a bit a of an Oracle newbie, and wanted to ask for an example of a Stored Procedure that I can use as the source of data for a report developed in Crystal Reports. I'd like to be able to pass the stored procedure 2 parameters (date and facility) and have it return the result set (date, facility, quantiy, calculations, etc.)
Thanks in advance
July 06, 2005 - 1:03 pm UTC