title: How can we create dynamic type in Oracle?
body: <p>I have a master table which contains information of lookup tables, base tables, master tables, primary columns, base columns. Using that information I am creating dynamic SQL statements and execute those via <code>execute immediate</code>.</p>
  
  <p>As there are many records in the transaction, I am using bulk collect. To collect records in bulk, I want to create dynamic types in Oracle at run-time based on the columns available in master.</p>
  
  <p>Below is code structure:</p>
  
  <pre><code>create or replace procedure pro_name(code varchar2,retmsg out varchar2)as
  rec_master scr_master_data%rowtype;
  type fstcurtype is ref cursor;
    cur_fname fstcurtype;
    v_sql varchar2(5000);
    v_ran_sql varchar2(5000);
  
  begin
  
  SELECT *
    INTO rec_master
    FROM SCR_MASTER_DATA
    WHERE SCREMBLING_CODE = P_SCREMBLING_CODE
    AND ACTIVE            ='Y';
  
   v_sql := 'select distinct '||rec_master.primary _columns||','||rec_master.base_columns||' from '||rec_master.base_tables;
  
    v_ran_sql:= 'select '||rec_master.LOOKUP_COLUMNS||' from (select /*+ parallel('||rec_master.LOOKUP_TABLES||',16) */ '||rec_master.LOOKUP_COLUMNS
                 ||' from '||rec_master.LOOKUP_TABLES ||' order by dbms_random.value ) where rownum = 1';
  
   OPEN CUR_FNAME for v_sql;
   LOOP
      FETCH CUR_FNAME BULK COLLECT INTO rec_fname LIMIT NVL(rec_master.ROWS_TO_COMMIT,10000) ;
  .
  .
  .
  .
  exception
  when others then
  .
  .
  end;
  </code></pre>
  
  <p>i want to create a type based on variable <code>v_sql</code>. Whatever columns mentioned in that statement should be used for the type.</p>
  
  <p>I tried to add below code after <code>v_ran_sql</code> as well but it was a waste of effort.</p>
  
  <pre><code>execute immediate 'type ty_name is record(row_id varchar2(500),fst_name varchar2(500),acc_number varchar2(500))';
  execute immediate 'rec_fname ty_name';
  </code></pre>
  
  <p>in which <code>row_id</code>, <code>fst_name</code>, <code>acc_number</code> column name will come from the master table.</p>
  
  <p>Is there any way around this?</p>