SQL> 
SQL> drop table user_tab_a;

Table dropped.

SQL> create table user_tab_a as select table_name,num_rows,last_analyzed
  2         from user_tables;

Table created.

SQL> 
SQL> -- This does not work
SQL> -- Similar to using 'bulk collect' within 'forall', cann't do that.
SQL> -- input 1 table, 1 array, output 1 array
SQL> declare
  2  type typ1 is table of user_tab_columns.column_name%type;
  3  type typ2 is table of number;
  4  x typ1;
  5  y typ2;
  6  begin
  7  select column_name bulk collect into x from user_tab_columns
  8         where table_name='USER_TAB_A';
  9  execute immediate
 10  'begin '||
 11  'forall i in :b1.first..:b1.last '||
 12  'select length(:b1(i)) into :b2(i) from user_tab_a; '||
 13  'end;'
 14  using x.first, x.last, out y;
 15  end;
 16  /
using x.first, x.last, out y;
                           *
ERROR at line 14:
ORA-06550: line 14, column 28: 
PLS-00457: expressions have to be of SQL types 
ORA-06550: line 9, column 1: 
PL/SQL: Statement ignored 


SQL> 
SQL> -- This works, but the only thing meaningful is that 'TEST%' can be other
SQL> -- dynamic string. Basically it is still
SQL> -- select table_column bulk collect into array from table_name
SQL> -- input 1 table, output 1 array
SQL> declare
  2  x num_tab_type;
  3  begin
  4  execute immediate 'begin '||
  5                    'select num_rows bulk collect into :b1 '||
  6                    ' from user_tables '||
  7                    'where table_name like '||''''||'TEST%'||''''||';'||
  8                    ' end;' using out x;
  9  dbms_output.put_line(x(1));
 10  end;
 11  /
23                                                                              

PL/SQL procedure successfully completed.

SQL> -- This works but is also pretty useless.
SQL> declare
  2  x num_tab_type;
  3  begin
  4  execute immediate 'select num_rows from user_tables ' bulk collect into x;
  5  dbms_output.put_line(x(1));
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> drop table user_tab_a;

Table dropped.

SQL> 
SQL> spool off