create or replace procedure utl_file_test_read (
path in varchar2,
filename in varchar2)
is
input_file utl_file.file_type;
input_buffer varchar2(4000);
begin
input_file := utl_file.fopen (path,filename, 'R');
declare
v_empno number(7);
v_first_name varchar2(30);
v_surname varchar2(30);
v_id_number number(13);
v_location number(30);
v_sex char(1);
v_job_title varchar2(30);
v_cost_code varchar2(40);
v_full_job_name varchar2(30);
v_perm_contr char(4);
v_start_date char(80);
v_end_date char(80);
v_dob char(10);
v_headc number(4,2);
v_job_category varchar2(30);
v_class char(2);
v_em char(2);
v_counter number;
v_1_delim number;
v_2_delim number;
v_3_delim number;
v_4_delim number;
v_5_delim number;
v_6_delim number;
v_7_delim number;
v_8_delim number;
v_9_delim number;
v_10_delim number;
v_11_delim number;
v_12_delim number;
v_13_delim number;
v_14_delim number;
v_15_delim number;
v_16_delim number;
begin
-- Start looping through txt file
v_counter := 0;
loop
begin
utl_file.get_line (input_file, input_buffer);
-- Get positions of all tabs/delimiters
v_1_delim := instr(input_buffer,chr(9),1,1);
v_2_delim := instr(input_buffer,chr(9),1,2);
v_3_delim := instr(input_buffer,chr(9),1,3);
v_4_delim := instr(input_buffer,chr(9),1,4);
v_5_delim := instr(input_buffer,chr(9),1,5);
v_6_delim := instr(input_buffer,chr(9),1,6);
v_7_delim := instr(input_buffer,chr(9),1,7);
v_8_delim := instr(input_buffer,chr(9),1,8);
v_9_delim := instr(input_buffer,chr(9),1,9);
v_10_delim := instr(input_buffer,chr(9),1,10);
v_11_delim := instr(input_buffer,chr(9),1,11);
v_12_delim := instr(input_buffer,chr(9),1,12);
v_13_delim := instr(input_buffer,chr(9),1,13);
v_14_delim := instr(input_buffer,chr(9),1,14);
v_15_delim := instr(input_buffer,chr(9),1,15);
v_16_delim := instr(input_buffer,chr(9),1,16);
-- Get field values from txt file
-- Ignore first record and only start loading from second record
if v_counter > 0 then --and v_empno is not null then
v_empno := trim(substr(input_buffer,1,v_1_delim -1));
v_first_name := trim(substr(input_buffer,v_1_delim +1,v_2_delim - v_1_delim - 1));
v_surname := trim(substr(input_buffer,v_2_delim +1,v_3_delim - v_2_delim - 1));
v_id_number := trim(substr(input_buffer,v_3_delim +1,v_4_delim - v_3_delim - 1));
v_location := trim(substr(substr(input_buffer,v_4_delim +1,v_5_delim - v_4_delim - 1),1,3));
v_sex := trim(substr(input_buffer,v_5_delim +1,v_6_delim - v_5_delim - 1));
v_job_title := trim(substr(input_buffer,v_6_delim +1,v_7_delim - v_6_delim - 1));
v_cost_code := trim(substr(input_buffer,v_7_delim +1,v_8_delim - v_7_delim - 1));
v_full_job_name := trim(substr(input_buffer,v_8_delim +1,v_9_delim - v_8_delim - 1));
v_perm_contr := trim(substr(substr(input_buffer,v_9_delim +1,v_10_delim - v_9_delim - 1),1,2));
v_start_date := trim(substr(input_buffer,v_10_delim +1,v_11_delim - v_10_delim - 1));
v_end_date := trim(substr(input_buffer,v_11_delim +1,v_12_delim - v_11_delim - 1));
v_dob := trim(substr(input_buffer,v_12_delim +1,v_13_delim - v_12_delim - 1));
-- Try to convert dob to MMDDYYYY but txt file data not consistent
--v_dob := trim(substr(v_dob,1,2)||substr(v_dob,4,2)||substr(v_dob,7));
v_headc := trim(substr(input_buffer,v_13_delim +1,v_14_delim - v_13_delim - 1));
v_job_category := trim(substr(input_buffer,v_14_delim +1,v_15_delim - v_14_delim - 1));
v_class := trim(substr(input_buffer,v_15_delim +1,v_16_delim - v_15_delim - 1));
v_class := trim(v_class)||trim(v_class);
v_em := trim(substr(input_buffer,v_16_delim +1));
-- Only not-null records, in case there is a blank line in the file...
insert into jaco_test nologging
values
( v_empno,
v_first_name,
v_surname,
v_id_number,
v_location,
v_sex,
v_job_title,
v_cost_code,
v_full_job_name,
v_perm_contr,
v_start_date,
v_end_date,
v_dob,
v_headc,
v_job_category,
v_class,
v_em);
end if;
v_counter := v_counter + 1;
exception when no_data_found then
exit;
end;
end loop;
utl_file.fclose(input_file);
end;
end;
/