Returning Oracle ref cursor and appending multiple results

Question

I have this problem I'm hoping someone knows the answer to. I have an oracle stored procedure that takes a customer id and returns all the customer's orders in a ref_cursor. Oversimplifying it, this is what I have:

Orders
- orderId
- siteID

Customers
- siteID
- Name

GetOrder(siteID, outCursor) /* returns all orders for a customer */

Now, I need to write another procedure that takes a customer name and does a LIKE query to get all custIds, then I need to reuse the GetOrder method to return all the orders for the custIds found, something like this:

   PROCEDURE GetOrderbyCustName(
      p_name       IN        VARCHAR2,
      curReturn    OUT       sys_refcursor
   )
   IS
      siteid    number;
   BEGIN
      FOR rec in SELECT site_id FROM customers WHERE name LIKE p_name
      LOOP 
      -- This will replace curReturn in each iteration
      -- how do I append instead?
        GetOrder(rec.site_id,
                   curReturn
                  );
      END LOOP;
   END GetOrderbyCustName;

My question is, how do I append the return of GetOrder to curReturn in each iteration? As it's written right now it overwrites it in each cycle of the loop. Thanks!!

Answer 1

You can't do it like that - cursors cannot be appended or merged. Just do this instead:

PROCEDURE GetOrderbyCustName(
   p_name       IN        VARCHAR2,
   curReturn    OUT       sys_refcursor
)
IS
BEGIN
   OPEN curReturn FOR 
      SELECT o.orderID, o.siteID
      FROM Orders o
      JOIN Customers c ON c.siteID = o.siteID
      WHERE c.name LIKE p_name;
END GetOrderbyCustName;