oracle - Re-using a query result in a PL/SQL package -

i have trouble writing sql queries. inside package function, trying reuse result of query in 2 other queries. here's how goes :

my schema stores requests. each request concerns multiple destinations. also, each request detailed in table (request_detail). in addition, requests identified ids.

so, using 3 tables. 1 requests, destinations , last 1 details. each 1 of theses tables indexed request_id column.

the query want optimize when user wants find requests, plus destinations , commands have been sent between 2 dates.

i want query request_table first in order request_ids. then, use request_ids list query command table , destination one.

i couldn't find how that... can't use ref cursors can't fetched twice... need array-like or column-like variable store request_ids, use variable twice or more...

here's original queries optimize :

    function extract_request_with_date (ze_from_date date, ze_to_date date, x_request_list out cursor_type, x_destination_list out cursor_type,                                         x_command_list out cursor_type) return varchar2          my_function_id  varchar2(80) := package_id || '.extract_request_with_date';         my_return_code  varchar2(2);     begin           open x_request_list         select name,destination_type,          success_cnt, status, status_description,         request_id, parent_request_id, deduplication_id, submit_date, last_update_date         app_db.request_table         submit_date >= ze_from_date             , submit_date < ze_to_date         order request_id;          open x_destination_list         select request_id, destination_id         app_db.destination_table         submit_date >= ze_from_date             , submit_date < ze_to_date         order request_id;           open x_command_list         select sequence_number, name, params, destination_id          send_date, last_update_date,process_cnt, status, status_description,         validity_period, to_abort_flag          app_db.request_details_table         submit_date >= ze_from_date             , submit_date < ze_to_date         order request_id, destination_id, sequence_number;          return return_ok;      end extract_request_with_date; 

as see, use same predicate (that submit_date conditions) 3 queries. think there's maybe way optimize getting request_ids using them in remaining queries.

thanks hearing me out !

based on queries posted i'd add submit_date index request_table, destination_table , request_details_table , leave sql is. 3 queries optimized , run fast matching against table of request_id values.