Examine all REF CURSOR outputs from stored procedure called in a loop

Question

I have a stored procedure which will return a REF CURSOR containing a certain block of data:

PROCEDURE MAIN_SP(PARAM1 IN NUMBER, 
                  PARAM2 IN VARCHAR2,
                     ..
                     P_REF_CURSOR OUT REF CURSOR)
BEGIN
..
   OPEN P_REF_CURSOR FOR
     SELECT V_SUCCESSFUL_COUNT, V_RESPONSE_CODE FROM DUAL;
END

Then there is an outer stored procedure which will call this main one in a loop. The stored procedure signature matches the inner one's with the exception that it takes an array of values, one of which gets substituted in a loop as one of the Main's params. In this example, it's PARAM2 in the main stored procedure.

PROCEDURE OUTER_SP(PARAM1 IN NUMBER,
                   P_REF_ID_LIST IN TABLE OF VARCHAR2,
                   ..,
                   P_REF_CURSOR OUT REF CURSOR)
BEGIN
       ..
       FOR i IN 1 .. P_REF_ID_LIST.COUNT LOOP
           MAIN_SP(PARAM1, P_REF_ID_LIST(i), ..,  P_REF_CURSOR);
       END
END

If this gets executed N times, am I correct that I only get the single last REF CURSOR block back? There's a V_RESPONSE_CODE included in each REF CURSOR output, which might contain an error. How would I collect and examine all the results to see if at least one has a V_RESPONSE_CODE > 0, for example, indicating an error? The outer stored procedure's REF CURSOR should be either a concatenated list or array of all the individual REF CURSORs.

Answer 1

Open each cursor, read the response and collect them into a collection, close the cursor and then create a new cursor for the collected results.

Something like:

CREATE TYPE response_obj IS OBJECT(
  count    NUMBER(10,0),
  response NUMBER(3,0)
);

CREATE TYPE response_tab IS TABLE OF response_obj;

PROCEDURE OUTER_SP(
  PARAM1        IN  NUMBER,
  P_REF_ID_LIST IN  TABLE OF VARCHAR2,
  -- ..,
  P_REF_CURSOR  OUT SYS_REFCURSOR
)
IS
  v_responses    response_tab := response_tab();
  v_cursor       SYS_REFCURSOR;
  v_count        PLS_INTEGER;
  v_response     PLS_INTEGER;
BEGIN
  -- ..
  FOR i IN 1 .. P_REF_ID_LIST.COUNT LOOP
    MAIN_SP(
      PARAM1,
      P_REF_ID_LIST(i),
      -- ..,
      v_cursor
    );
    FETCH v_cursor INTO v_count, v_response;
    CLOSE v_cursor;
    v_responses.EXTEND;
    v_responses(v_responses.COUNT) := response_obj(v_count, v_response);
  END LOOP;

  OPEN p_ref_cursor FOR
    SELECT * FROM TABLE(v_responses);
END;