UTL_FILE example

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;