You can use pipelined table function to fetch the data so that you do not have to wait for the PL/SQL collection to get ready till the last row is fetched. And then in a bunch/limit of lets say 1000 at a time do the DML on the respective tables. You can use FORALL and BULK operations for faster result. To handle the exceptions you would have to use SQL%BULK_EXCEPTIONS.

Alternatively, to my above mentioned approach, you can use Result Cache as well. It is a 11g feature. It is also applicable for cross session availability.

could you do your original query and collect its result into some kind of collection and then cast this collection into a table when you want to do your inserts, deletes etc.)

DECLARE
   employee_ids   SYS.odcinumberlist;
BEGIN
   SELECT employee_id
     BULK COLLECT INTO employee_ids
     FROM employees
    WHERE employee_id < 200;

   FOR c
      IN (SELECT *
            FROM employees e,
                 (SELECT COLUMN_VALUE FROM TABLE (employee_ids)) t
           WHERE e.employee_id = t.COLUMN_VALUE)
   LOOP
      DBMS_OUTPUT.put_line (c.employee_id || ' ' || c.first_name);
   END LOOP;
END;

Pipelined table function would be better than this, OP can build the pipeline function as a dynamic sql and execute it wherever within the scope of the session for any type of DML.

You can do is define a collection at session level for example in your package.

Fill the Collection with the data you want.

You can use this particular Collection as many times you want in your session.

Pipelined functions are not always better. I have seen in one of my application where a pipelined function is used to generate just numbers which is then used to derive date ranges. But the SELECT query that uses these dates seems to do better if the date is generated just by using DUAL and CONNECT BY clause.

So we could say that it all depends how and where we implement. If the number of records that OP wanted to store in cache is minimal (maybe 10 or 100 etc) then it would be better to store it in a collection. Pipelined function could be used if there are thousands of rows.

And do Pipelined function cache results so that the results could be re-used in more than one place in a procedure..?

I think in that scenario you will need a nested table because I don't think you can cast an associative array into a table.

I believe the collections that can be cast into table are varray(the one in my example) and nested tables.

so unfortunately you will need to define your nested table outside of the procedure.

or you could cheat and select your column values into a varray where the column values are deliminated by something

so select column1||'.'||column2  bulk collect into myvarray

I do this .... er I mean I've heard some people do  this sometimes.

If there are more than one columns, you would need to create an Object type at database level. Then create a collection of that object type in the procedure.

Example:

CREATE OR REPLACE TYPE "OBJ1" as OBJECT(
        column1 varchar2(256 CHAR),
        column2 varchar2(35 CHAR)
        );

CREATE OR REPLACE TYPE "nt_obj1" as table of OBJ1; -- this could be done at procedure level as well

DECLARE
   t_employee_ids   nt_obj1;
BEGIN
   SELECT OBJ1(column1,column2)
     BULK COLLECT INTO  t_employee_ids
     FROM table1
    WHERE column3 = NNN
.............
.....

think I'm missing something here but I can't quite figure it out

CREATE TYPE employee_info AS OBJECT
(
   employee_id NUMBER (6),
   employee_first_name VARCHAR2 (20 BYTE),
   employee_last_name VARCHAR2 (25 BYTE)
);


CREATE OR REPLACE TYPE employee_info_table IS TABLE OF employee_info;



DECLARE
   employee_ids   employee_info_table;
BEGIN
   SELECT EMPLOYEES.EMPLOYEE_ID,
          EMPLOYEES.FIRST_NAME,
          EMPLOYEES.LAST_NAME  BULK COLLECT
     INTO employee_ids (employee_id, employee_first_name, employee_last_name)
     FROM employees
    WHERE employee_id < 200;
END;

ORA-06550: line 7, column 11:
PLS-00316: PL/SQL TABLEs must use a single index
ORA-06550: line 8, column 6:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 4, column 4:
PL/SQL: SQL Statement ignored

More like this (untested)...

DECLARE
   employee_ids employee_info_table;
BEGIN
   SELECT employee_ids (
             e.employee_id,
             e.first_name,
             e.last_name)
   BULK COLLECT
   INTO employee_ids
   FROM employees e
   WHERE employee_id < 200;
END;

never mind I got it

drop type employee_info_table;
drop type employee_info;
CREATE TYPE employee_info AS OBJECT
(
   employee_id NUMBER (6),
   first_name VARCHAR2 (20 BYTE),
   last_name VARCHAR2 (25 BYTE)
);


CREATE OR REPLACE TYPE employee_info_table IS TABLE OF employee_info;;



DECLARE
   employee_ids   employee_info_table;
BEGIN

   SELECT employee_info(employee_id, first_name, last_name)
    bulk collect INTO employee_ids
     FROM employees
    WHERE employee_id < 200
    order by employee_id;
END;

ok I think it is something like this

CREATE TYPE employee_info AS OBJECT
(
   employee_id NUMBER (6),
   first_name VARCHAR2 (20 BYTE),
   last_name VARCHAR2 (25 BYTE)
);


CREATE OR REPLACE TYPE employee_info_table IS TABLE OF employee_info;

;



DECLARE
   employee_ids   employee_info_table;
BEGIN
     SELECT employee_info (employee_id, first_name, last_name)
       BULK COLLECT INTO employee_ids
       FROM employees
      WHERE employee_id < 200
   ORDER BY employee_id;


   FOR c IN (SELECT e.*
               FROM employees e, TABLE (employee_ids) t
              WHERE e.employee_id = t.employee_id)
   LOOP
      DBMS_OUTPUT.put_line (c.employee_id || ' ' || c.first_name);
   END LOOP;
END;