Stored procedure return multiple collections?

[lemkepf]

Hey all! Now i'll let you know i have no knowledge of oracle at all. (i'm asking this because our oracle guy is out of town for like 3 weeks and we need an answer ASAP).
We have an ASP page we are using to get information out of oracle. Right now we are just executing simple select statements to get stuff back.
We are making 9 select statements for multple things on the page. We would like to speed this page up by using a stored procedure.
Here is the problem, some selects return 1 row, others return 15, but all with the same primary key (a product number)
We want to limit the amout of calls to oracle, so we were wondering....
Can you return multiple collections (or recordsets) with one call and one stored procedure?
What do you think?
Thanks a ton!
Paul

 

[LKBrwnDBA]

And I don't know anything about ASP, but you can return collections and recordsets from a strored procedure.

An example of code to define and return a recordset would be:

-- Definitions:
Type rec_set_type is record
(prod_cd varchar2(10)
,prod_desc varchar2(32)
,prod_price number(10,2)
);
Type rec_set is table of rec_set_type;

-- Usage in a procedure:
Create Procedure MyProcedure
(P_rec_set IN OUT rec_set_type)
Is
Begin
<< populate rec_set >>
end;
/

HTH

NOTE: The 'IN OUT' specifies the recordset will be returned.
This is not the only way to return recordsets, a stored procedure can also return cursors and arrays.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Oooops,

(P_rec_set IN OUT rec_set_type)
should read:
(P_rec_set IN OUT rec_set)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb