How to create text file with UTL_FILE

To create text file,as a developer we use "UTL_FILE" concept in oracle.

Lets get complete details on this topic.

What's UTL_FILE:- Its a Built in package ,which is used to create text file in server or read data from text file at OS level.
  
To create/read text file at OS level , User should have privileges to access that particular directory.

So for our eg: let's create Directory to create text file in that location.

SQl>CREATE OR REPLACE DIRECTORY UTL_DIR AS '/tmp/Utl_Dir'  ;

If you don't have permissions to create directory then ask your DBA to create and provide full permissions(READ,WRITE,EXECUTE) on it.

 Now let's say you may wanted to write emp table data to text file as below:

Note:

File format will be plain text with pipe delimiter between each field. There will not be any fixed length for fields in text file.

And say if you get requirement for filename as "EMP_REP_DD-MM-YYYY_SEQ-NUMBER(4 digits).

 

SQL> CREATE SEQUENCE EMP_SEQ START WITH 1 MAXVALUE 100 NOCACHE;

We create above sequence to get sequential values for file name as per client requirement.  

Create one procedure as below to create file.

CREATE OR REPLACE PROCEDURE Emp_Utl_File IS
  FILE_HANDLE      UTL_FILE.file_type;
  V_DIR            ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := 'UTL_DIR';
  V_DIRECTORY_PATH ALL_DIRECTORIES.DIRECTORY_PATH%TYPE;
  V_filename       VARCHAR2(100);
  V_SQL            VARCHAR2(32767);
BEGIN
  SELECT DIRECTORY_PATH
    INTO V_DIRECTORY_PATH
    FROM ALL_DIRECTORIES
   WHERE DIRECTORY_NAME = V_DIR;

  DBMS_OUTPUT.PUT_LINE('Dir path ' || V_DIRECTORY_PATH);

  V_filename := 'EMP_REP' || '_' || TO_CHAR(SYSDATE, 'DD-MM-YYYY') || '_' ||
                LPAD(EMP_SEQ.NEXTVAL, 4, 0);

DBMS_OUTPUT.PUT_LINE('File name is  ' || V_filename );

 --Create/Open file in READ mode
  FILE_HANDLE := UTL_FILE.fopen(location     => V_DIRECTORY_PATH,
                                                   filename     => V_filename,
                                                   open_mode    => 'W',
                                                    max_linesize => 32767);

-- max_linesize => 32767--Don't ignore this parameter if you need more amount of data for each line,Because Default value is 1024 (i.e) it means You are telling oracle that I am not expecting more than 1024 characters in a line So use it for  maximum size allowed ie. 32767.  

  FOR K IN (SELECT * FROM EMP) LOOP

--Using loop,writing one by one line to text file
    V_SQL := K.EMPNO || '|' || K.ENAME || '|' || K.SAL || '|' || K.DEPTNO;
 
    UTL_FILE.put_line(file => FILE_HANDLE, buffer => V_SQL);

  END LOOP;

  IF UTL_FILE.is_open(file => FILE_HANDLE) THEN
    UTL_FILE.fclose(FILE_HANDLE);
  END IF;

EXCEPTION

--Handling Multiple exceptions which are related to UTL_FILE
  WHEN UTL_FILE.INVALID_PATH THEN
    UTL_FILE.FCLOSE_ALL;
    RAISE_APPLICATION_ERROR(-20051, 'Invalid Path');
 
  WHEN UTL_FILE.INVALID_MODE THEN
    UTL_FILE.FCLOSE_ALL;
    RAISE_APPLICATION_ERROR(-20052, 'Invalid Mode');
 
  WHEN UTL_FILE.INTERNAL_ERROR THEN
    UTL_FILE.FCLOSE_ALL;
    RAISE_APPLICATION_ERROR(-20053, 'Internal Error');
 
  WHEN UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.FCLOSE_ALL;
    RAISE_APPLICATION_ERROR(-20054, 'Invalid Operation');
 
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.FCLOSE_ALL;
    RAISE_APPLICATION_ERROR(-20055, 'Invalid Operation');
 
  WHEN UTL_FILE.WRITE_ERROR THEN
    UTL_FILE.FCLOSE_ALL;
    RAISE_APPLICATION_ERROR(-20056, 'Invalid Operation');
END;
 

Execute:

BEGIN
Emp_Utl_File;
END;

We can find "UTL_FILE packages units" here.

I hope you understood basic concept to create text file using UTL_FILE,feel free to ask questions if you have any.Thanks.