Bulk Collection example

FOR i IN row.FIRST..row.LAST
LOOP

   SELECT row2
   BULK COLLECT INTO row_coll2
   FROM table 2
   WHERE row2 = row(i);

   FOR z IN row_coll2.FIRST..row_coll2.LAST
   LOOP
      row_coll2_keeper.EXTEND;
      row_coll2_keeper(row_coll2_keeper.COUNT) := row_coll2(x);
   END LOOP; 
  
END LOOP;

 

Use user defined types instead
For example

SQL> set serveroutput on
SQL>
SQL> create or replace type typ_coll is table of number;
  2  /

Type created
SQL> create or replace type typ_coll2 is table of varchar2(30);
  2  /

Type created
SQL> declare
  2  row_coll typ_coll;
  3  row_coll2 typ_coll2;
  4 
  5  begin
  6  SELECT deptno
  7  BULK COLLECT INTO row_coll
  8  FROM emp;
  9 
10  SELECT dname
11  BULK COLLECT INTO row_coll2
12  FROM dept
13 WHERE deptno in (select column_value from table(row_coll));
14 
15  for i in 1..row_coll2.count
16  loop
17  dbms_output.put_line(row_coll2(i));
18  end loop;
19  end;

20  /

RESEARCH
SALES
ACCOUNTING

PL/SQL procedure successfully completed

SQL>

 

Or without user defined types:

declare   

    type row_coll_tab is table of dept%rowtype;    
    row_coll    row_coll_tab;    
    type row_coll2_tab is table of emp%rowtype;    
    row_coll2_temp   row_coll2_tab;   
    row_coll2        row_coll2_tab := row_coll2_tab();   


begin   
    select *   
    bulk collect into row_coll     
    from dept;       

   for i in 1 .. row_coll.count   
   loop      
       select *        
       bulk collect into row_coll2_temp        
       from emp       
       where deptno = row_coll (i).deptno;           

       row_coll2 := row_coll2 multiset union all row_coll2_temp;           

    end loop;    

end;/