Combining data from cursors into one

I have a stored procedure that executes another stored procedure several times. I need to combine and return the data that I have after completing the second procedure.

Can I somehow combine data from multiple cursors into one of the cursors? Is this possible without temporary tables or a table data type?

EDIT: the number of cursors to combine is actually n (where n is 1, 2, 3, etc., detecting by another procedure).

For instance:

CREATE OR REPLACE PROCEDURE proc_data
( data_out OUT SYS_REFCURSOR
) IS
BEGIN
 OPEN data_out FOR SELECT '1' NUM FROM dual;
END;
/

CREATE OR REPLACE PROCEDURE proc_result
( data_out OUT SYS_REFCURSOR
) IS
 data1 SYS_REFCURSOR;
 data2 SYS_REFCURSOR;
BEGIN
 PROC_DATA(data1);
 PROC_DATA(data2);
 -- select data1 and data2 into data_out - how?
END;
/

SET SERVEROUTPUT ON

DECLARE 
 data_out SYS_REFCURSOR;
 temp_row VARCHAR2(10);
BEGIN
 PROC_RESULT(data_out);
  LOOP
    FETCH data_out INTO temp_row;
    EXIT WHEN data_out%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(temp_row);
  END LOOP;
  CLOSE data_out;
END;
/

expected output:

---
1   
1   
+5
source share
2 answers

, . AskTom , , , , .

+2

, ,

select table(PROC_DATA(data1)) union table(PROC_DATA(data2))
+3

All Articles