SQL & PL/SQL

I have the following problem. I am an experienced Java programmer but am a bit of a n00b at SQL and PL/SQL.

I need to do the following.

1 Pass in a few arrays and some other variables into a procedure

2 Loop on the values in the arrays (they all have the same number of items) and dynamically create an SQL statement

3 Run this statement and add it to the result set (which is an OUT parameter of the procedure)


I already have experience of creating an SQL query on the fly, running it and adding the result to a result set (which is a REF CURSOR) but I'm not sure how I'd loop and add the results of each call to the query to the same result set. I'm not even sure if this is possible.

Here's what I have so far (code edited for simplicity). I know it's wrong because I'm just replacing the contents of the RESULT_SET with the most recent query result (and this is being confirmed in the Java which is calling this procedure).

Any and all help would be greatly appreciated.

TYPE REF_CURSOR IS REF CURSOR;




PROCEDURE GET_DATA_FASTER(in_seq_numbers IN seq_numbers_array, in_values IN text_array, in_items IN text_array, list IN VARCHAR2, RESULT_SET OUT REF_CURSOR) AS
query_str VARCHAR2(4000);

seq_number NUMBER;
the_value VARCHAR2(10);
the_item VARCHAR2(10);

BEGIN

FOR i IN 1..in_seq_numbers.COUNT
LOOP

seq_number := in_seq_numbers(i);
the_value := trim(in_values(i));
the_item := trim(in_items(i));

query_str := 'SELECT distinct '||seq_number||' as seq, value, item
FROM my_table ai';

query_str := query_str || '
WHERE ai.value = '''||the_value||''' AND ai.item = '''||the_item||'''
AND ai.param = ''BOOK''
AND ai.prod in (' || list || ');

OPEN RESULT_SET FOR query_str;

END LOOP;

EXCEPTION WHEN OTHERS THEN
RAISE;

END GET_DATA_FASTER;