TechnicalBits

• UTL_FILE is an oracle pl/sql package that is supplied to allow PL/SQL to read and create text files in the file system.
• UTL_FILE can only read and create text files. Specifically, it cannot be used to read or create binary files.
• UTL_FILE is an appropriate tool for creating reports in the form of  flat file from the database. UTL_FILE is also used  for reading files.

The following steps must be followed in order to run UTL_FILE package.

Step 1) Connect as sys database user

Step 2) Create a directory:

SQL> CREATE OR  REPLACE DIRECTORY utl_file_dir  AS ‘E:\PLSQL’;

Directory created.
In the above query utl_file_dir  is the logical name for the path ‘E:\PLSQL’.

• We can mention the logical name utl_file_dir  inside the program in uppercase within single quotes (utl_file_dir is mapped to the directory ‘E:\PLSQL’)

• We can create any number of logical path names(DBA directories) in oracle 10g.

• A DBA user needs to grant you access to read from/ write to a specific directory before using this package. Here is an example:

CONNECT / AS SYSDBA
GRANT read, write ON DIRECTORY utl_file_dir TO scott;

• Provide user access to the UTL_FILE package (created by catproc.sql):

GRANT EXECUTE ON UTL_FILE TO scott;

Consider following example that performs read and write operations on file from client machine to server machine:

Note: In Client Server File operations please make your source and destination folder shared to avoid issues while directory creation.

CREATE OR REPLACE DIRECTORY server_dir AS '\\NINANI-D01\joss_upload';
GRANT READ, WRITE ON DIRECTORY server_dir TO scott;

CREATE OR REPLACE DIRECTORY client_dir AS '\\APATIL-D01\joss_upload';
GRANT READ, WRITE ON DIRECTORY client_dir TO scott;

DECLARE
 V1 VARCHAR2(32767);
 F_READ UTL_FILE.FILE_TYPE;
 F_WRITE UTL_FILE.FILE_TYPE;
 v_count number:=0;
BEGIN
  F_READ := UTL_FILE.FOPEN('CLIENT_DIR','Extract.csv','R');
  F_WRITE := UTL_FILE.FOPEN('SERVER_DIR', 'Extract.csv', 'W');
 
    BEGIN
    LOOP
       UTL_FILE.GET_LINE(F_READ,V1);
       UTL_FILE.PUT_LINE(F_WRITE, V1);
       v_count:=v_count+1;
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('End of file..');
  END;   
 
  UTL_FILE.FCLOSE(F_read);
  UTL_FILE.FCLOSE(F_WRITE);
 
   DBMS_OUTPUT.PUT_LINE(V_COUNT||' Records Inserted');
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END ;