Build TYPE for many columns

Question

I have below type:

TYPE t_my_list is record(col1 VARCHAR2(4000),col2 varchar2(4000),col3 varchar2(4000));
Type  listOfString is table of t_my_list;

then Im doing BULK COLLECT:

EXECUTE IMMEDIATE v_stmt BULK COLLECT INTO v_ret;

What if I have 20 columns? Doing it as below I think don't make sense:

TYPE t_my_list is record(col1 VARCHAR2(4000),col2 varchar2(4000),....col20 varchar2(200);

How else build the TYPE?

UPDATE:

declare
  TYPE                         t_my_list is record(colX VARCHAR2(4000),colY varchar2(4000),ColZ varchar2(4000));
  Type                         listOfString  is table of t_my_list;
  v_stmt      VARCHAR2(32000) := 'SELECT col1, col2, col2 FROM table_TEST';
  v_ret                        listOfString  := listOfString ();

begin
   EXECUTE IMMEDIATE v_stmt BULK COLLECT INTO v_ret;
   --DBMS_OUTPUT.PUT_LINE('v_ret = '||v_ret.count);   
    for i in v_ret.first..v_ret.last loop
       DBMS_OUTPUT.PUT_LINE('colX: '||v_ret (i).colX||', colY: '||v_ret (i).colY||', ColZ: '||v_ret (i).ColZ);
       --will be done second EXECUTE IMMEDIATE taking as parameter ColX, ColY and ColZ
    end loop;
end;

Answer 1

It doesn't look like you really need to use dynamic SQL, so you can declare a cursor statically, then use that cursor's %rowtype to create a collection type:

declare
  cursor c_my_cursor is select col1, col2, col3 from table_test;
  type t_my_table is table of c_my_cursor%rowtype;
  v_ret t_my_table;
begin
  open c_my_cursor;
  fetch c_my_cursor bulk collect into v_ret;
  close c_my_cursor;

  --DBMS_OUTPUT.PUT_LINE('v_ret = '||v_ret.count);   
  for i in v_ret.first..v_ret.last loop
    DBMS_OUTPUT.PUT_LINE('col1: '||v_ret (i).col1||', col2: '||v_ret (i).col2||', Col3: '||v_ret (i).Col3);
  end loop;
end;
/

This doesn't loop over the cursor, it just opens it, does a single bulk fetch into the collection, and closes it again.

If you are selecting the same column twice for some reason, or if you just want to stick to the X/Y/Z naming (or whatever scheme you want), you can just alias the columns in the cursor query:

  cursor c_my_cursor is select col1 as colx, col2 as coly, col3 as colz from table_test;
...
    DBMS_OUTPUT.PUT_LINE('colX: '||v_ret (i).colX||', colY: '||v_ret (i).colY||', ColZ: '||v_ret (i).ColZ);