SQL & PL/SQL

Hi folks,

Is there a way to make a dynamic procedure to work with .CSV documents and store it into a collection? For example you have to make a procedure to read from .CSV but users upload 10 different version that have different number of columns.

Normally I would define a record type to match those columns and store it into a collection. However if I don't know the number of columns I would need to define 10 records in advance which I am trying to avoid.

Problem is I cant define SQL elements on the fly. Meaning on production I don't have the rights to dynamically create a table to match my columns and then drop the table after I no longer need it so I need to store data into a collection.

And the last option where I would loop through the document and then do the operations I need is not good since the document is a part of other procedures that write and read from it. The idea is to pick the data, store it into a collection, close the file and then work with it.

This is what I got so far:

 
declare 
  ------------------------------------------------------------------------------ 
  -- Variables 
  ------------------------------------------------------------------------------ 
  l_file      utl_file.file_type; 
  l_line      varchar2(10000); 
  l_string    varchar2(32000); 
  l_delimiter varchar2(10); 

  ------------------------------------------------------------------------------ 
  -- Types 
  ------------------------------------------------------------------------------ 
  type r_kolona is record( 
    column_1 varchar2(500) 
   ,column_2 varchar2(500) 
   ,column_3 varchar2(500) 
   ,column_4 varchar2(500) 
   ,column_5 varchar2(500)); 
  type t_column_table is table of r_kolona; 
  t_column    t_column_table := t_column_table(); 
begin 
  /*Define the delimiter*/ 
  l_delimiter := ';'; 
  /*Open file*/ 
  l_file      := utl_file.fopen( 'some dir', 'some.csv', 'R'); 
  /*Takes first row of document as header*/ 
  utl_file.get_line( l_file, l_line); 

  loop 
    begin 
      utl_file.get_line( l_file, l_line); 
      /*Delete newline operator*/ 
      l_string                         := rtrim( l_line, chr(13)) || l_delimiter; 
      /*Extend array and insert parsed values */ 
      t_column.extend; 
      t_column(t_column.last).column_1 := substr( l_string, 1, instr( l_string, l_delimiter, 1, 1) - 1); 
      t_column(t_column.last).column_2 := substr( l_string, instr( l_string, l_delimiter, 1, 1) + 1, instr( l_string, l_delimiter, 1, 2) - instr( l_string, l_delimiter, 1, 1) - 1); 
      t_column(t_column.last).column_3 := substr( l_string, instr( l_string, l_delimiter, 1, 2) + 1, instr( l_string, l_delimiter, 1, 3) - instr( l_string, l_delimiter, 1, 2) - 1); 
      t_column(t_column.last).column_4 := substr( l_string, instr( l_string, l_delimiter, 1, 3) + 1, instr( l_string, l_delimiter, 1, 4) - instr( l_string, l_delimiter, 1, 3) - 1); 
      t_column(t_column.last).column_5 := substr( l_string, instr( l_string, l_delimiter, 1, 4) + 1, instr( l_string, l_delimiter, 1, 5) - instr( l_string, l_delimiter, 1, 4) - 1); 
    exception 
      when no_data_found then 
        exit; 
    end; 
  end loop; 

  /*Close file*/ 
  utl_file.fclose(l_file); 

  /*Loop through collection elements*/ 
  for i in t_column.first .. t_column.last 
  loop 
    dbms_output.put_line( 
         t_column(i).column_1 
      || ' ' 
      || t_column(i).column_2 
      || ' ' 
      || t_column(i).column_3 
      || ' ' 
      || t_column(i).column_4 
      || ' ' 
      || t_column(i).column_5); 
  end loop; 
exception 
  when others then 
    utl_file.fclose(l_file); 
end; 

Stupid version would be to define a record with 50 elements and hope they dont nuke the excel with more columns :)

Best regards,
Igor