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