SQL> create table t(vc1 varchar2(100), d1 date, n1 number);

Table created.

SQL> begin
  2  for i in 2 .. 100 loop
  3    execute immediate 'alter table t add  vc'||i||' varchar2(100)';
  4    execute immediate 'alter table t add  d'||i||' date';
  5    execute immediate 'alter table t add  n'||i||' number';
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace
  2  procedure my_collection_generator(p_query varchar2) is
  3    l_theCursor integer default dbms_sql.open_cursor;
  4    l_columnValue varchar2(4000);
  5    l_status integer;
  6    l_descTbl dbms_sql.desc_tab;
  7    l_colCnt number;
  8    l_cs varchar2(255);
  9    l_date_fmt varchar2(255);
 10
 11    l_row t%rowtype;
 12
 13    l_char_arr sys.odcivarchar2list := sys.odcivarchar2list();
 14    l_date_arr sys.odcidatelist := sys.odcidatelist();
 15    l_num_arr  sys.odcinumberlist := sys.odcinumberlist();
 16  begin
 17
 18    execute immediate 'alter session set nls_date_format = ''yyyymmddhh24miss''';
 19
 20    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
 21    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 22
 23    for i in 1 .. l_colCnt loop
 24      if ( l_descTbl(i).col_type not in ( 113 ) )
 25      then
 26        dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
 27      end if;
 28    end loop;
 29
 30    l_status := dbms_sql.execute(l_theCursor);
 31
 32    while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
 33    loop
 34      l_char_arr := sys.odcivarchar2list();
 35      l_date_arr := sys.odcidatelist();
 36      l_num_arr  := sys.odcinumberlist();
 37      for i in 1 .. l_colCnt loop
 38        if l_descTbl(i).col_type in ( 2,100,101 ) then
 39          dbms_sql.column_value( l_theCursor, i, l_columnValue );
 40          l_num_arr.extend;
 41          l_num_arr(l_num_arr.count) := l_columnValue;
 42        elsif l_descTbl(i).col_type in ( 12,13 ) then
 43          dbms_sql.column_value( l_theCursor, i, l_columnValue );
 44          l_date_arr.extend;
 45          l_date_arr(l_date_arr.count) := l_columnValue;
 46        else
 47          dbms_sql.column_value( l_theCursor, i, l_columnValue );
 48          l_char_arr.extend;
 49          l_char_arr(l_char_arr.count) := l_columnValue;
 50        end if;
 51      end loop;
 52
 53      if l_date_arr.exists(1) then l_row.d1 := l_date_arr(1); end if;
 54      if l_date_arr.exists(2) then l_row.d2 := l_date_arr(2); end if;
 55      if l_date_arr.exists(3) then l_row.d3 := l_date_arr(3); end if;
 56      if l_date_arr.exists(4) then l_row.d4 := l_date_arr(4); end if;
 57      ...
 58      if l_date_arr.exists(100) then l_row.d100 := l_date_arr(100); end if;
 59
 60      if l_num_arr.exists(1) then l_row.n1 := l_num_arr(1); end if;
 61      if l_num_arr.exists(2) then l_row.n2 := l_num_arr(2); end if;
 62      if l_num_arr.exists(3) then l_row.n3 := l_num_arr(3); end if;
 63      if l_num_arr.exists(4) then l_row.n4 := l_num_arr(4); end if;
 64     ...
 65      if l_num_arr.exists(100) then l_row.n4 := l_num_arr(100); end if;
 66
 67      if l_char_arr.exists(1) then l_row.vc1 := l_char_arr(1); end if;
 68      if l_char_arr.exists(2) then l_row.vc2 := l_char_arr(2); end if;
 69      if l_char_arr.exists(3) then l_row.vc3 := l_char_arr(3); end if;
 70      if l_char_arr.exists(4) then l_row.vc4 := l_char_arr(4); end if;
 71       ...
 72      if l_char_arr.exists(100) then l_row.vc4 := l_char_arr(100); end if;
 73
 74      insert into t values l_row;
 75    end loop;
 76
 77    dbms_sql.close_cursor( l_theCursor);
 78  exception
 79    when others then
 80      dbms_sql.close_cursor( l_theCursor);
 81      raise;
 82  end;
 83  /

Procedure created.

SQL> sho err
No errors.
SQL>
SQL>
SQL> exec my_collection_generator('select * from emp');

PL/SQL procedure successfully completed.