Use UTL_FILE to read from a file and insert into the lecturer table.

  

CREATE TABLE lecturer (
  id               NUMBER(5) PRIMARY KEY,
  first_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  major            VARCHAR2(30),
  current_credits  NUMBER(3)
  );

INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);

INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
              VALUES (10002, 'Mar', 'Wells','History', 4);

INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);

INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10004, 'Man', 'Kyte','Economics', 8);

INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10005, 'Pat', 'Poll','History', 4);

INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
              VALUES (10006, 'Tim', 'Viper','History', 4);



CREATE OR REPLACE PROCEDURE Loadlecturer (
  p_FileDir  IN VARCHAR2,
  p_FileName IN VARCHAR2,
  p_TotalInserted IN OUT NUMBER) AS

  v_FileHandle UTL_FILE.FILE_TYPE;
  v_NewLine  VARCHAR2(100);  -- Input line
  myFirstName lecturer.first_name%TYPE;
  v_LastName lecturer.last_name%TYPE;
  v_Major lecturer.major%TYPE;

  v_FirstComma NUMBER;
  v_SecondComma NUMBER;

BEGIN
  v_FileHandle := UTL_FILE.FOPEN(p_FileDir, p_FileName, 'r');

  p_TotalInserted := 0;

  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;

    v_FirstComma := INSTR(v_NewLine, ',', 1, 1);
    v_SecondComma := INSTR(v_NewLine, ',', 1, 2);

    myFirstName := SUBSTR(v_NewLine, 1, v_FirstComma - 1);
    v_LastName := SUBSTR(v_NewLine, v_FirstComma + 1,
                         v_SecondComma - v_FirstComma - 1);
    v_Major := SUBSTR(v_NewLine, v_SecondComma + 1);

    INSERT INTO lecturer (ID, first_name, last_name, major) VALUES (1, myFirstName, v_LastName, v_Major);

    p_TotalInserted := p_TotalInserted + 1;
  END LOOP;

  UTL_FILE.FCLOSE(v_FileHandle);

  COMMIT;
EXCEPTION
  WHEN UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20051, 'Loadlecturer: Invalid Operation');
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20052, 'Loadlecturer: Invalid File Handle');
  WHEN UTL_FILE.READ_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20053, 'Loadlecturer: Read Error');
  WHEN UTL_FILE.INVALID_PATH THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20054, 'Loadlecturer: Invalid Path');
  WHEN UTL_FILE.INVALID_MODE THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20055, 'Loadlecturer: Invalid Mode');
  WHEN UTL_FILE.INTERNAL_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20056, 'Loadlecturer: Internal Error');
  WHEN VALUE_ERROR THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE_APPLICATION_ERROR(-20057, 'Loadlecturer: Value Error');
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE;
END Loadlecturer;
/

drop table lecturer;

Related examples in the same category

1.	Writing "Hello World!" to a File
2.	Use UTL_FILE.PUT_LINE to write a line of text to a file
3.	Use utl_file.fopen to open a file
4.	Use UTL_FILE.FCLOSE to close a file
5.	Use UTL_FILE to print a transacript to a file.
6.	Catch different UTL_FILE related errors
7.	Save data in a cursor to a file
8.	Load the data into table myTable. You may use any valid number for column c1's data.

Use UTL_FILE to read from a file and insert into the lecturer table. : UTL_FILE « System Packages « Oracle PL / SQL

Related examples in the same category