Complex Cursors for returning multiple rows

Question

Each row in a cursor should be joined with another table and the whole result should be returned a one cursor

Say cursor1 return 5 rows. Each of these 5 rows should be joined with table1. and the final result for all the 5 rows should be returned a 1 row.

Plz help

Answer 1

It is rather tricky to combine the fetched records from a ref cursor with the result set of another query. A much better idea would be to open just the one ref cursor with a SELECT which joins all the relevant tables.

Answer 2

The question isn't clear, but it sounds like what you need is something like this

a) Define an object type with the shape of your result row b) Define a collection of that object type c) Create a function with a return type of the collection type - this could take in cursor A as a parameter (SYS_REFCURSOR), join each row in cursor A to table B, and then use PIPE ROW for each result row. d) If the final result is needed as a cursor, then another function along the lines of

 FUNCTION complex_query(in_cursor SYS_REFCURSOR) 
 RETURN SYS_REFCURSOR
 IS
     lreturn SYS_REFCURSOR;
 BEGIN
     OPEN lreturn FOR
        (SELECT * FROM TABLE(convert_to_collection(in_cursor)));
     RETURN lreturn;
 END;

Alternatively, you could just do the SELECT * FROM TABLE(convert_to_collection(in_cursor)) directly.

What I don't understand is the requirement that everything is returned as 1 row.