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);
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
source
share