UTL_FILE Read Example

This script can be used to read data from a flat file and insert into a table.

UTL_FILE Read

CREATE OR REPLACE PROCEDURE load_data(errbuff varchar2,errcode number)

AS

v_line VARCHAR2(2000); -- Data line read from input file

v_file UTL_FILE.FILE_TYPE; -- Data file handle

v_dir VARCHAR2(250); -- Directory containing the data file

v_filename VARCHAR2(50); -- Data filename

v_1st_Comma number;

v_2nd_Comma number;

v_3rd_Comma number;

v_4th_Comma number;

v_5th_Comma number;

 

v_empno sample_emp.empno%type;

v_ename sample_emp.ename%type;

v_job sample_emp.job%type;

v_mgr sample_emp.mgr%type;

v_hiredate sample_emp.hiredate%type;

--v_sal sample_emp.sal%type;

 

BEGIN

 

v_dir := '/usr/tmp';

v_filename := 'sample.dat';

v_file := UTL_FILE.FOPEN(v_dir, v_filename, 'r');

 

-- --------------------------------------------------------

-- Loop over the file, reading in each line. GET_LINE will

-- raise NO_DATA_FOUND when it is done, so we use that as

-- the exit condition for the loop.

-- --------------------------------------------------------

 

LOOP

BEGIN

 

UTL_FILE.GET_LINE(v_file, v_line);

 

EXCEPTION

WHEN no_data_found THEN

exit;

END;

 

-- ----------------------------------------------------------

-- Each field in the input record is delimited by commas. We

-- need to find the locations of the two commas in the line,

-- and use these locations to get the fields from v_line.

-- ----------------------------------------------------------

 

v_1st_Comma := INSTR(v_line, ',' ,1 , 1);

v_2nd_Comma := INSTR(v_line, ',' ,1 , 2);

v_3rd_Comma := INSTR(v_line, ',' ,1 , 3);

v_4th_Comma := INSTR(v_line, ',' ,1 , 4);

v_5th_Comma := INSTR(v_line, ',' ,1 , 5);

 

v_empno := to_number(SUBSTR(v_line, 1, v_1st_Comma-1));

v_ename := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1);

v_job := SUBSTR(v_line, v_2nd_comma+1, v_3rd_Comma-v_2nd_Comma-1);

v_mgr := to_number(SUBSTR(v_line, v_3rd_comma+1, v_4th_Comma-v_3rd_Comma-1));

v_hiredate := to_date(SUBSTR(v_line, v_4th_comma+1, v_5th_Comma-v_4th_Comma-1),

'DD-MON-YYYY');

-- v_sal := to_number(SUBSTR(v_line, v_5th_comma+1),'99999');

 

DBMS_OUTPUT.PUT_LINE(v_empno ||' '|| v_ename || ' ' || v_job || ' ' || v_mgr ||' ' || v_hiredate);

 

-- ------------------------------------------

-- Insert the new record into the DEPT table.

-- ------------------------------------------

 

INSERT INTO sample_emp

VALUES (v_empno,v_ename,v_job,v_mgr,v_hiredate);

END LOOP;

 

UTL_FILE.FCLOSE(v_file);

 

COMMIT;

 

END;