One way to combine results from multiple PL/SQL cursors using temporary tables

 Sometimes is necessary to join results from two or more Oracle PL/SQL cursors into one cursor.
Modifying existing cursor using joining different tables (or views) from other cursors can  be difficult and error prone process because we changes how existing, tested cursors works.
One way to do it is using Oracle temporary tables.

Problem:
- have two or  more pl/sql cursors
- want to merge their results without changing original cursors

Solutions:
Insert results from every cursor into temporary table and than read data from it with new cursor.

Example:

First cursor:
cursor1   ref_cursor;
open cursor1 for
        select * from table1;

Second cursor:
cursor2   ref_cursor;
open cursor2 for
       select * from table2;

Create temporary table (same structure as table1 and table2):

create global temporary table GTT$TEMP_TBL
(
id NUMBER,
name VARCHAR2(50),
size NUMBER
)
on commit delete rows;

The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

Define new variable of   GTT$TEMP_TBL  rowtype: 

    row_temp   gtt$temp_tbl%rowtype;

Copy data from both cursors into temporary table:

LOOP
    FETCH cursor1
       INTO row_temp
    EXIT WHEN cursor1%NOTFOUND;

    INSERT INTO gtt$temp_tbl VALUES row_temp;
END LOOP;

LOOP
   FETCH cursor2
      INTO row_temp
   EXIT WHEN cursor2%NOTFOUND;

   INSERT INTO gtt$temp_tbl VALUES row_temp;

END LOOP;

Finally, we can get joined results using one "super cursor":

 p_result    ref_cursor;

OPEN p_result FOR
      select * from gtt$ temp_tbl;