Return multiple recordsets in ORACLE stored procedures
Posted on 2009-04-22
I am fairly new to the ORACLE stored procedures and I have the following issue. I am using 10g (talked to c#/ASP.NET) and need to return both customer records and product records for EACH customer.
The return recordset(or collections) is something like this conceptually:
(and so on)
Is that possible to use ORACLE stored proc to return that sort of results? If I can return that sort of result in ONE go then I don't have to do a round trip to database to get product code each time I get a customer record.....
I have got a bit of (imaginary) code here:
PROCEDURE DUMMY (........ P_RSET OUT CSR_RSET, P_RSET OUT CSR_RSET2)
both CSR_RSET and CSR_RSET2 are ref cursors
OPEN P_RSET FOR
FROM CUSTOMER _TABLE;
OPEN P_RSET2 FOR
FROM PRODUCT_TABLE WHERE CUST_ID = P_RSET.CUST_ID
I am sure it is wrong but can someone guides me? Thanks in advance. Any question pls ask