Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Union data from cursors into one

I have stored procedure which executes another stored procedure several times. I need union and return data, which I have after executing second procedure.

Can I in some way union data from several cursors into one another cursor? It is possible without temporary tables or table-like datatype?

EDIT: Cursor count for union actually is n (where n is 1, 2, 3, etc, detecting by another procedure).

For example:

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   
like image 349
ksogor Avatar asked Nov 03 '10 07:11

ksogor


2 Answers

No, it's not possible. There's a nice discussion at AskTom regarding this question, take a look maybe some workarounds provided there can help you.

like image 145
andr Avatar answered Sep 28 '22 06:09

andr


You can achieve this by creating a pipelined function which will allow you to do

select table(PROC_DATA(data1)) union table(PROC_DATA(data2))
like image 44
vc 74 Avatar answered Sep 28 '22 07:09

vc 74