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;

Answer 2

If the query is simple, I would say go with Tony's answer. This is not only simple but likely to perform better than executing one query for each siteID.

If it is fairly complex then it might be worth some extra effort to reuse the GetOrder procedure so you only have to maintain one query.

To do this, you would need to actually fetch the data from the refcursor on each iteration of the loop, and put it into some other data structure.

One option, if it makes sense for the interface, is to change GetOrderbyCustName to have a PL/SQL index-by table as its output parameter instead of a refcursor. Append to that table on each iteration through the loop.

If you really need to return a refcursor, you can use a nested table type instead and then return a cursor querying that nested table. Something like this (not tested code):

CREATE TYPE number_table_type AS TABLE OF NUMBER;

PROCEDURE GetOrderbyCustName(
      p_name       IN        VARCHAR2,
      curReturn    OUT       sys_refcursor
   )
   IS
      cursor_source_table  number_table_type := number_table_type();
      single_site_cursor  sys_refcursor;
      orderID  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,
                   single_site_cursor
                  );

        -- Fetch all rows from the refcursor and append them to the nested table in memory
        LOOP
          FETCH single_site_cursor INTO orderID;
          EXIT WHEN single_site_cursor%NOTFOUND;
          cursor_source_table.extend();
          cursor_source_table( cursor_source_table.COUNT+1) := orderID;
        END LOOP;
      END LOOP;

      OPEN curReturn FOR
        SELECT * FROM TABLE( cursor_source_table );

   END GetOrderbyCustName;