SQL> 
SQL> -- BULK COLLECT works db link in 9.2.0.1
SQL> 
SQL> set serveroutput on
SQL> 
SQL> -- For 8i
SQL> 
SQL> create or replace type o_typ_1 as object (object_name VARCHAR2(128), object_type VARCHAR2(18));
  2  /

Type created.

SQL> create or replace type t_o_typ_1 as table of o_typ_1;
  2  /

Type created.

SQL> 
SQL> declare
  2  x t_o_typ_1;
  3  begin
  4  select cast(multiset(select object_name,object_type from user_objects@phoenix_scott
  5              where rownum<10) as t_o_typ_1) into x from dual;
  6  for i in 1..x.count loop
  7    dbms_output.put_line(x(i).object_name||' '||x(i).object_type);
  8  end loop;
  9  end;
 10  /
ADDRESS_O TYPE                                                                  
ADDRESS_T TYPE                                                                  
BONUS TABLE                                                                     
DEPT TABLE                                                                      
EMP TABLE                                                                       
I_SNAP$_MV2_MVTEST INDEX                                                        
I_SNAP$_MV_NESTED_FAST INDEX                                                    
JYANG_SNAP_TEST TABLE                                                           
JYANG_SNAP_TEST MATERIALIZED VIEW                                               

PL/SQL procedure successfully completed.

SQL> 
SQL> -- For 9i
SQL> declare
  2  type typ1 is table of user_objects.object_name%Type;
  3  x typ1;
  4  begin
  5  select object_name BULK COLLECT into x from user_objects@phoenix_scott
  6         where rownum<10;
  7  for i in 1..x.count loop
  8    dbms_output.put_line(x(i));
  9  end loop;
 10  end;
 11  /
ADDRESS_O                                                                       
ADDRESS_T                                                                       
BONUS                                                                           
DEPT                                                                            
EMP                                                                             
I_SNAP$_MV2_MVTEST                                                              
I_SNAP$_MV_NESTED_FAST                                                          
JYANG_SNAP_TEST                                                                 
JYANG_SNAP_TEST                                                                 

PL/SQL procedure successfully completed.

SQL> 
SQL> -- Syntax error here...
SQL> declare
  2  x t_o_typ_1;
  3  begin
  4  select object_name, object_type BULK COLLECT into x
  5         from user_objects@phoenix_scott where rownum<10;
  6  for i in 1..x.count loop
  7    dbms_output.put_line(x(i).object_name||' '||x(i).object_type);
  8  end loop;
  9  end;
 10  /
       from user_objects@phoenix_scott where rownum<10;
       *
ERROR at line 5:
ORA-06550: line 5, column 8: 
PL/SQL: ORA-00947: not enough values 
ORA-06550: line 4, column 1: 
PL/SQL: SQL Statement ignored 


SQL> 
SQL> declare
  2  x t_o_typ_1;
  3  begin
  4  select o_typ_1(object_name, object_type) BULK COLLECT into x
  5         from user_objects@phoenix_scott where rownum<10;
  6  for i in 1..x.count loop
  7    dbms_output.put_line(x(i).object_name||' '||x(i).object_type);
  8  end loop;
  9  end;
 10  /
ADDRESS_O TYPE                                                                  
ADDRESS_T TYPE                                                                  
BONUS TABLE                                                                     
DEPT TABLE                                                                      
EMP TABLE                                                                       
I_SNAP$_MV2_MVTEST INDEX                                                        
I_SNAP$_MV_NESTED_FAST INDEX                                                    
JYANG_SNAP_TEST TABLE                                                           
JYANG_SNAP_TEST MATERIALIZED VIEW                                               

PL/SQL procedure successfully completed.

SQL> 
SQL> -- Without using database type, use PL/SQL record type
SQL> declare
  2  type rec_type is record (
  3       object_name user_objects.object_name%type,
  4       object_type user_objects.object_type%type);
  5  type tab_o_rec is table of rec_type;
  6  x tab_o_rec;
  7  begin
  8  select object_name, object_type BULK COLLECT into x
  9         from user_objects@phoenix_scott where rownum<10;
 10  for i in 1..x.count loop
 11    dbms_output.put_line(x(i).object_name||' '||x(i).object_type);
 12  end loop;
 13  end;
 14  /
ADDRESS_O TYPE                                                                  
ADDRESS_T TYPE                                                                  
BONUS TABLE                                                                     
DEPT TABLE                                                                      
EMP TABLE                                                                       
I_SNAP$_MV2_MVTEST INDEX                                                        
I_SNAP$_MV_NESTED_FAST INDEX                                                    
JYANG_SNAP_TEST TABLE                                                           
JYANG_SNAP_TEST MATERIALIZED VIEW                                               

PL/SQL procedure successfully completed.

SQL> 
SQL> drop type t_o_typ_1;

Type dropped.

SQL> drop type o_typ_1;

Type dropped.

SQL> 
SQL> spool off