SQL & PL/SQL

Hi,

I need to extract some data from a few tables.

First I use a select query with joins between tables, and then use the data obtained from this first select query in the second select query.

In the end I need to send back a cursor (as it can easily be received as a ResultSet in Java) with all the required data that must have some of the data from the first select statement and the data from the second select statement in a single row, and there are multiple rows of such data that need to be sent back.

If I use a loop for the second select statement (after opening the cursor that is the first select statement) I can print out these values (dbms_output), including data from the first select statement but how do I assign all the rows to a cursor that I want to return along with values from the first select statement?

To make myself clearer, I have created a sample code similar to the scenario discussed above. Any inputs will be very helpful, thanks.

-- *****************************
TYPE report_cursor IS REF CURSOR;
FUNCTION getUserReport RETURN report_cursor;
-- *****************************

FUNCTION getUserReport RETURN report_cursor
IS

user_data_cursor report_cursor;
temp_userno number;
temp_group_name Varchar2(50);

Cursor referencelist Is

SELECT t1.userno, t2.group_name
FROM table_1@extdb1 t1, table_2@extdb1 t2
WHERE t1.table1_id = t2.id
AND (upper(t2.group_name) LIKE 'AAA%')
and t1.userno IN (select userno
from table3@extdb1 where ATTEND_FLAG='Y'
AND base_userno IS NOT NULL)
order by t2.group_name;

Begin

open referencelist;

-- LOOP
Fetch referencelist Into temp_userno, temp_group_name;
--Exit when referencelist%NOTFOUND;

OPEN user_data_cursor FOR

select userno, class_id, auth_info
from table3@extdb1 where ATTEND_FLAG='Y'
AND base_userno IS NOT NULL
AND userno = temp_userno
order by product_id, base_userno;

-- Need to send each row back, this way only one record goes back in the cursor.
-- Need to send back t2.group_name also for each row, how to do so?

RETURN user_data_cursor;
-- End Loop;

END getUserReport;

-- *****************************