CREATE OR REPLACE PROCEDURE load_data 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_deptno      NUMBER;
  v_dname       VARCHAR2(14);
  v_loc         VARCHAR2(13);

  BEGIN

    v_dir       := '/u01/app/oracle/common/out';
    v_filename  := 'dept01.csv';
    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_deptno     := SUBSTR(v_line, 1, v_1st_Comma-1);
      v_dname      := SUBSTR(v_line, v_1st_Comma+1, v_2nd_Comma-v_1st_Comma-1);
      v_loc        := SUBSTR(v_line, v_2nd_Comma+1);

      DBMS_OUTPUT.PUT_LINE(v_deptno || ' - ' || v_dname || ' - ' || v_loc);

      -- ------------------------------------------
      -- Insert the new record into the DEPT table.
      -- ------------------------------------------

      INSERT INTO dept
        VALUES (v_deptno, UPPER(v_dname), UPPER(v_loc));

    END LOOP;

    UTL_FILE.FCLOSE(v_file);
    COMMIT;

  END;
/