UTL_FILE in Usage

Uses of UTL_FILE package:

• In Oracle PL/SQL, UTL_FILE is an Oracle supplied package which is used for file operations (read and write) in conjunction with the underlying operating system.
• UTL_FILE works for both server and client machine systems. A directory has to be created on the server, which points to the target file.
• For the files located on the server machine, the actual path can be given while creating the directory.
• For the files which are located on the client machines, however, the relative path is required along with the client machine name.
• In addition, the relative file path must be in shared mode with read and write access for the required users.
• A DBA must create the directory and then grant Read/Write access to the required users.

How to create file in executing pl/sql procedure by using UTL_FILE package:

Pre-requisites to perform the aove mentioned functionality

1. Directory should present in the database

2. Directory should have the read/write permissions 

3. Inorder to get the directory name we will use table named dba_directories

Code to create CSV file which is taking the values from table in database by using UTL_FILE

DECLARE

   CURSOR stud_cur

   IS

      SELECT *  FROM student;

      v1_file   UTL_FILE.file_type;

      lv_directory VARCHAR2(30) := 'VENDFILES'; -- Directory Name

      lv_file      VARCHAR2(30) := 'test_JACK.csv';  -- File Name

BEGIN

   v1_file := UTL_FILE.fopen (lv_directory, lv_file, 'w');

   FOR stud_rec IN stud_cur

   LOOP

      UTL_FILE.put_line (v1_file, '"' || stud_rec.student_id || '","' || stud_rec.student_name ||'","' || stud_rec.address ||'","' || stud_rec.marks || '"');

   END LOOP;

   UTL_FILE.fclose (v1_file);

EXCEPTION

   WHEN UTL_FILE.invalid_path

   THEN

      fnd_file.put_line (fnd_file.LOG, 'INVALID PATH' || SQLERRM);

   WHEN UTL_FILE.invalid_mode

   THEN

      fnd_file.put_line (fnd_file.LOG, 'THE OPEN_MODE PARAMETER IN FOPEN WAS INVALID' || SQLERRM);

   WHEN UTL_FILE.invalid_filehandle

   THEN

      fnd_file.put_line (fnd_file.LOG, ' THE FILE HANDLE WAS INVALID' || SQLERRM);

   WHEN UTL_FILE.invalid_operation

   THEN

      fnd_file.put_line (fnd_file.LOG,'THE FILE COULD NOT BE OPENED OR OPERATED ON AS REQUESTED' || SQLERRM );

   WHEN UTL_FILE.write_error

   THEN

      fnd_file.put_line (fnd_file.LOG,'AN OPERATING SYSTEM ERROR OCCURRED DURING THE WRITE OPERATION' || SQLERRM);

   WHEN UTL_FILE.internal_error

   THEN

      fnd_file.put_line (fnd_file.LOG, 'AN UNSPECIFIED ERROR IN PL/SQL' || SQLERRM);

   WHEN NO_DATA_FOUND

   THEN

      fnd_file.put_line (fnd_file.LOG, 'NO DATA FOUND' || SQLERRM);

   WHEN OTHERS

   THEN

      fnd_file.put_line (fnd_file.LOG, 'OTHERS ERROR' || SQLERRM);

END;

Read the file content using UTL_FILE

set serveroutput on;

DECLARE

 vInHandle utl_file.file_type;

 vNewLine  VARCHAR2(250);

 lv_directory VARCHAR2(30) := 'VENDFILES'; -- Directory

 lv_file      VARCHAR2(30) := 'student.dat'; -- Data file

BEGIN

  vInHandle := utl_file.fopen(lv_directory, lv_file, 'R');

  LOOP

    BEGIN

      utl_file.get_line(vInHandle, vNewLine);

      dbms_output.put_line(vNewLine);

    EXCEPTION

      WHEN OTHERS THEN

        EXIT;

    END;

  END LOOP;

  utl_file.fclose(vInHandle);

END;