[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1881
  • Last Modified:

Return multiple recordsets in ORACLE stored procedures

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:

===================
customer1    product1
                     product2
                     product3
===================
customer2    product2
                     product3
===================
(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
      SELECT CUST_ID
      FROM CUSTOMER _TABLE;

OPEN P_RSET2 FOR
     SELECT PRODUCT_CODE
     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

0
waiman
Asked:
waiman
  • 6
  • 3
  • 2
1 Solution
 
sdstuberCommented:
how about  returning only 1 recordset that all the data you want...

outer join the products to the customers  (note the (+))  


open rset for
   select c.cust_id, p.product_code from customer_table c, product_table  p where c.cust_id = p.cust_id (+)

or, if you prefere ansi syntax


open rset for
   select c.cust_id, p.product_code from customer_table c left outer join product_table  p on c.cust_id = p.cust_id
0
 
sventhanCommented:
YES.
This can be done.
-- sample code (not tested)

CREATE OR REPLACE PROCEDURE DUMMY (CUSTOMERS OUT RESULT1, PRODUCT S OUT RESULT2) AS
TYPE cus_ref IS REF CURSOR ;
TYPE prd_ref IS REF CURSOR;
P_RSET cus_ref;
P_RSET2 prd_ref;
BEGIN
OPEN P_RSET FOR
SELECT CUST_ID
FROM CUSTOMER _TABLE;

OPEN P_RSET2 FOR
SELECT PRODUCT_CODE
FROM PRODUCT_TABLE WHERE CUST_ID = P_RSET.CUST_ID
-------
------
END;
/
0
 
sdstuberCommented:
This...


OPEN P_RSET2 FOR
SELECT PRODUCT_CODE
FROM PRODUCT_TABLE WHERE CUST_ID = P_RSET.CUST_ID


should generate the following errors
PLS-00487: Invalid reference to variable 'P_RSET'
and
ORA-00904: "P_RSET"."CUST_ID": invalid identifier

because cust_id is an element of a fetched value, not of the reference cursor itself.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
you can't open a second reference cursor relative to an unknown value.
0
 
sventhanCommented:
I agree.
I did not pay close attention to the where clause of the 2nd cursor.
SD was right.
0
 
sdstuberCommented:
another option is to NOT filter the second recordset but instead sort both of them.
then iterate through each and match the records as you walk through them.

The easiest though is to simply return a single cursor that already has your data grouped together.
0
 
waimanAuthor Commented:
I was told ORACLE can handle "nested cursor", will that work as well?
0
 
sdstuberCommented:
yes,
 you could do something like this... (still just using a single reference cursor)

OPEN P_RSET FOR
      SELECT CUST_ID, CURSOR( SELECT p.PRODUCT_CODE
                                                    FROM PRODUCT_TABLE p
                                                  WHERE p.CUST_ID = c.CUST_ID)  products                                  
      FROM CUSTOMER _TABLE c;

However, you may find the products nested table less convenient to work with.
0
 
waimanAuthor Commented:
refer to sdstuber:
Please excuse my ignorance so in your example how to refer to individual item in ASP.NET (C#)?  Can you provide a brief example.  Thanks in advance.
0
 
waimanAuthor Commented:
Sorry I think I should rephrase the question to sdstuber....
If I use the cursor expression as in your example... how can I use the ASP.NET C# to go through the records in the inner loops (i.e. the products)...thanks
0
 
sdstuberCommented:
sorry, that's what I meant by less convenient.  :)

I'm not a C# or asp guy so I can't help there much (at all)

easiest way, and with little or no downside would be to use a cursor like in my initial post, rather than the nested cursor.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now