giovedì 16 gennaio 2014

Oracle: import XLS file into Oracle stable



How to import an XLS file into an Oracle Table:


 procedure read_date_amount(p_filename in varchar2, pr_date out date, pr_amount out number) as  
  fp utl_file.file_type;  
  v_line varchar2(16384 char);  
  v_relevant_lines clob := '';  
  v_distance number := null;  
  v_filename varchar2(16384 char) := p_filename;  
  v_ret NUMBER;  
 begin  
  BEGIN  
   fp := utl_file.fopen(g_cs2_cmd_folder, v_filename, 'r', 16384);  
  exception when utl_file.invalid_operation then  
   write_log('Couldn''t open file ' || v_filename);  
   return;  
  end;  
  loop  
   begin  
    utl_file.get_line(fp, v_line);  
    if v_distance is null and v_line like '%INFOH%' then  
     v_distance := 0;  
     v_relevant_lines := substr(v_line, instr(v_line, 'INFOH'));  
    elsif v_distance is not null then  
     v_relevant_lines := v_relevant_lines || ' ' || v_line;  
     v_distance := v_distance + 1;  
     if v_distance = 5 then  
      pr_date := to_date(substr(v_relevant_lines, instr(v_relevant_lines, '<Data ss:Type="String">', 1, 7) + 23, 10), 'dd.mm.yyyy');  
      pr_amount := to_number(substr(v_relevant_lines, instr(v_relevant_lines, '<Data ss:Type="Number">', 1, 1) + 23, 10));  
      exit;  
     end if;  
    end if;      
   exception when no_data_found then  
    exit;  
   end;  
  end loop;  
  utl_file.fclose(fp);  
 end;  

Nessun commento:

Posta un commento