Returning Oracle ref cursor and appending multiple results

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)ISBEGIN   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;

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;