SQL> SQL> select * from test2; C1 C2 -- ---------- b 2 c 3 e 1 K 6 P 0 G 0 ap 9 u 7 8 rows selected. SQL> select * from test3; C1 C2 -- ---------- b 2 c 3 e 4 f 5 o 9 p 1 y 0 7 rows selected. SQL> create or replace view t_vw as select * from test2 union all select * from test3; View created. SQL> SQL> -- No problem BULK COLLECTing from a view SQL> set serveroutput on SQL> declare 2 type x is table of test2%rowtype; 3 y x; 4 begin 5 select * bulk collect into y from t_vw; 6 dbms_output.put_line('rows loaded: '||y.count); 7 end; 8 / rows loaded: 15 PL/SQL procedure successfully completed. SQL> SQL> declare 2 x num_tab_type; 3 y number; 4 begin 5 -- Bulk Collect NUMBER(1) into "table of NUMBER", no problem 6 select c2 bulk collect into x from t_vw; 7 select sum(t.column_value) into y from table(cast(x as num_tab_type)) t; 8 dbms_output.put_line('sum= '||y); 9 -- No need to CAST with 9i 10 select sum(t.column_value) into y from table(x) t; 11 dbms_output.put_line('sum= '||y); 12 -- column_value in WHERE clause 13 select count(1) into y from table(x) t where t.column_value >0; 14 dbms_output.put_line('count(1) t.column_value > 0 : '||y); 15 end; 16 / sum= 52 sum= 52 count(1) t.column_value > 0 : 12 PL/SQL procedure successfully completed. SQL> SQL> -- BULK COLLECT from Join collection with a table SQL> declare 2 type char_type is table of varchar2(2); 3 x num_tab_type; 4 x1 num_tab_type; 5 y char_type; 6 y1 char_type; 7 w num_tab_type; 8 icount number; 9 begin 10 select c2 bulk collect into x from test2; 11 select t1.c1 bulk collect into y from test3 t1, table(x) t2 where 12 t1.c2=t2.column_value; 13 for i in y.first..y.last loop 14 dbms_output.put_line('y('||i||')= '||y(i)); 15 end loop; 16 select t1.c1 bulk collect into y from test3 t1, table(x) t2 where 17 t1.c2=t2.column_value and t1.c1>'c'; 18 dbms_output.put_line('-----------------'); 19 for i in y.first..y.last loop 20 dbms_output.put_line('y('||i||')= '||y(i)); 21 end loop; 22 end; 23 / y(1)= b y(2)= c y(3)= p y(4)= y y(5)= y y(6)= o ----------------- y(1)= p y(2)= y y(3)= y y(4)= o PL/SQL procedure successfully completed. SQL> SQL> -- group by column_value SQL> declare 2 type char_type is table of varchar2(2); 3 x num_tab_type; 4 x1 num_tab_type; 5 y1 char_type; 6 begin 7 select c2 bulk collect into x from test2; 8 select count(1), t1.column_value BULK COLLECT into x1, y1 9 from table(x) t1 group by t1.column_value ; 10 dbms_output.put_line('-----------------'); 11 for i in y1.first..y1.last loop 12 dbms_output.put_line(y1(i)||' '||x1(i)); 13 end loop; 14 end; 15 / ----------------- 0 2 1 1 2 1 3 1 6 1 7 1 9 1 PL/SQL procedure successfully completed. SQL> SQL> -- TABLE function works with SQL, not PL/SQL, collection type SQL> declare 2 type num_type is table of number; 3 x num_type; 4 y number; 5 begin 6 select c2 bulk collect into x from t_vw; 7 --select sum(t.column_value) into y from table(x) t; 8 --dbms_output.put_line('sum= '||y); 9 10 select sum(t.column_value) into y from table(cast(x as num_tab_type)) t; 11 dbms_output.put_line('sum= '||y); 12 end; 13 / select sum(t.column_value) into y from table(cast(x as num_tab_type)) t; * ERROR at line 10: ORA-06550: line 10, column 51: PLS-00642: local collection types not allowed in SQL statements ORA-06550: line 10, column 51: PL/SQL: ORA-00932: inconsistent datatypes: expected - got CHAR ORA-06550: line 10, column 1: PL/SQL: SQL Statement ignored SQL> SQL> SQL> drop view t_vw; View dropped. SQL> SQL> spool off