Saturday, June 4, 2016

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;

1 comment:

DHEERAJ KAIN said...

Very good example of UTL file method

Post a Comment

Best Blogger TipsGet Flower Effect