utl_file read example

utl_file read example

In this post , We will be discuss about the utl_file read example. utl_file is the standard procedure given by oracle to read and write any txt or csv file in oracle system. with the help of utl_file , we can open any file in the oracle server and then do our read and write operations on that. utl_file is one of the best standard procedure given by oracle. Most of the outbound integration in which we do create some csv files from oracle system developed through using utl_file procedure. Here below is the detail utl_file read example.

Detail Working utl_file read example

Declare 

  p_FileDir VARCHAR2(400) := 'test_file_directory' ;--directory name---

  p_FileName VARCHAR2(400) := 'test.txt';

  v_FileHandle UTL_FILE.FILE_TYPE;

  v_NewLine  VARCHAR2(100); 

  supplierFirstName lecturer.first_name%TYPE;

 supplierLastName lecturer.last_name%TYPE;

  v_Major lecturer.major%TYPE;

  v_Comma1 NUMBER;

  v_Comma2 NUMBER;

BEGIN

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

  LOOP

    BEGIN

      UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);

    EXCEPTION

      WHEN NO_DATA_FOUND THEN

        EXIT;

    END;

    v_Comma1 := INSTR(v_NewLine, ',', 1, 1);

    v_Comma2 := INSTR(v_NewLine, ',', 1, 2);

    supplierFirstName := SUBSTR(v_NewLine, 1, v_FirstComma - 1);

    supplierLastName := SUBSTR(v_NewLine, v_FirstComma + 1,

                         v_SecondComma - v_FirstComma - 1);

    INSERT INTO supplier_details (ID, first_name, last_name, major) VALUES (1,supplierFirstName,supplierLastName);

  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');

END;