UTL_FILE - Script to Read Data From a Flat File and Insert into a Table

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;