How To Use UTL_FILE in Oracle

This online tutorial is based on examples to make it easier to follow. Oracle UTL_FILE allows to read from a text file and write into a text file. The files will be handled depending on the operation system where the database is installed. For example a text file created by Oracle package UTL_FILE in Unix or Linux system can have strange characters inside when you are opening the file using the Windows file editors. Still we do think the most file editors should be compatible with all operation systems and different characters will be handled correctly in systems.

The syntax to write into file using Oracle anonymous PL/SQL block is following:

DECLARE  
  <your_file_variable> UTL_FILE.FILE_TYPE; 
BEGIN 
  <your_file_variable> := UTL_FILE.FOPEN('ORACLE_DIRECTORY',
                                         '<your_file_name>',
                                         'W',
                                         32760);
  UTL_FILE.PUT_LINE(<your_file_variable>,<your_text>); 
  UTL_FILE.FCLOSE(<your_file_variable>); 
END;

To do writing or reading you need to create an Oracle directory as the following example:

SQL> CREATE DIRECTORY MY_DIR AS '/tmp'; 
SQL> GRANT READ ON DIRECTORY MY_DIR TO my_user; 
SQL> GRANT WRITE ON DIRECTORY MY_DIR TO my_user;

The created directory is using a Linux operation system and it is pointing to directory /tmp. If you are using Windows server then you should declare it as something like C:/temp and make sure the directory does exist and it has read-write permissions to everybody. The Oracle directory name is MY_DIR.

When you tried to create directory with your normal user in the way below and you got this error then you are missing permissions.

Error starting at line 1 in command:
CREATE DIRECTORY my_dir AS '/tmp'
Error at Command Line:1 Column:0
Error report:
SQL Error: ORA-01031: insufficient privileges

To fix it you need to give more permissions to you user using the SYSTEM user. You would need GRANT CREATE ANY DIRECTORY.

GRANT CREATE ANY DIRECTORY TO <username>;

After giving the missing permissions you can create the Oracle directory using your user.

SQL> CREATE DIRECTORY MY_DIR AS '/tmp';

Now we got the directory created and we can try to write something into a file. The most simple way to write into a text file is the following script and we will write text “online tech support computer help” into file onlinetechsupport.txt.

DECLARE  
  w_file UTL_FILE.FILE_TYPE;
BEGIN
  -- open file in writing mode and make it empty
  w_file := UTL_FILE.FOPEN('MY_DIR','onlinetechsupport.txt','W',32760);
  -- write text 'online tech support computer help' into file
  UTL_FILE.PUT_LINE(w_file,'online tech support computer help');
  -- close file
  UTL_FILE.FCLOSE(w_file); 
END;

We would like to remind you to do ALWAYS close to the file you have opened for reading or writing even with raised errors. Use the following example to make sure the file is always closed when the procedure has finished and to do so we have created procedure close_file that has been added even into the EXCEPTION WHEN OTHERS block.

DECLARE  
  w_file UTL_FILE.FILE_TYPE; 

  -- procedure to close the file
  PROCEDURE close_file
  IS
  BEGIN
    UTL_FILE.FCLOSE(w_file);
  EXCEPTION 
    WHEN OTHERS THEN -- when the file never opened do not raise an error
         null;
  END close_file;
BEGIN
  -- open file in writing mode and make it empty
  w_file := UTL_FILE.FOPEN('MY_DIR','onlinetechsupport.txt','W',32760);
  -- write text 'online tech support computer help' into file
  UTL_FILE.PUT_LINE(w_file,'online tech support computer help');
  -- close file
  close_file;
EXCEPTION 
    WHEN OTHERS THEN
         close_file; -- on error close the file
         raise; -- raise the error
END;

The following example shows how to write into a file without overwriting it every time. The difference is in the file opening mode that will be this time “A” as appending. The example will add only one line to the file and you will see every time an additional line with text “online tech support computer problems” in it after executing the following code.

DECLARE  
  w_file UTL_FILE.FILE_TYPE; 

  -- procedure to close the file
  PROCEDURE close_file
  IS
  BEGIN
    UTL_FILE.FCLOSE(w_file);
  EXCEPTION 
    WHEN OTHERS THEN -- when the file never opened do not raise an error
         null;
  END close_file;
BEGIN
  -- open file in writing mode and make it empty
  w_file := UTL_FILE.FOPEN('MY_DIR','onlinetechsupport.txt','A',32760);
  -- write text 'online tech support computer problems' into file
  UTL_FILE.PUT_LINE(w_file,'online tech support computer help');
  -- close file
  close_file;
EXCEPTION 
    WHEN OTHERS THEN
         close_file; -- on error close the file
         raise; -- raise the error
END;

Oracle hints: writing to file is very slow process and takes much time. The main reason is that the Oracle UTL_FILE procedures are very slow because they are mostly doing something with I/O or in other-words package UTL_FILE Oracle is using the server hard-drive (I/O) and this is the most slowest part of a computer. To make your file writing process faster you need to use the Oracle UTL_FILE package as less as possible.

This script below is a tuned version of writing into file. Basically the procedure is collecting all texts into a text variable named W_TEXT and writes it into a file when the variable gets full using procedure WRITE_TO_FILE. We will write 5000 lines into a file. To make sure the file is complete the last line gets written into the file without any checking. To make sure the lines will keep their format you can use character CHR(10) as the “next line” symbol.

DECLARE  
  w_file      UTL_FILE.FILE_TYPE;
  w_next_line VARCHAR2(1);
  w_text      VARCHAR2(32700);

  -- procedure to close the file
  PROCEDURE close_file
  IS
  BEGIN
    UTL_FILE.FCLOSE(w_file);
  EXCEPTION 
    WHEN OTHERS THEN -- when the file never opened do not raise an error
         null;
  END;

  -- Write after the variable gets full
  PROCEDURE write_to_file(p_text VARCHAR2)
  IS   
  BEGIN
    w_text := w_text||w_next_line||p_text;
  EXCEPTION 
    WHEN VALUE_ERROR THEN -- when the variable is full then write into file
      UTL_FILE.PUT_LINE(w_file,w_text);
      w_text := p_text;
  END;
BEGIN
  -- open file in writing mode and make it empty
  w_file := UTL_FILE.FOPEN('MY_DIR','onlinetechsupport.txt','W',32760);

  -- write 5000 lines into the file
 FOR i IN 1..5000 
 LOOP
 write_to_file(' computer help computer technician computer problems');
   -- do not add an empty line in the beginning of file 
    w_next_line := CHR(10);
 END LOOP;

  -- write the last text to complete file
  UTL_FILE.PUT_LINE(w_file,w_text);
  -- close file
  close_file;
EXCEPTION 
    WHEN OTHERS THEN
         close_file; -- on error close the file
         raise; -- raise the error
END;

Take a look at the code above to save time it doesn’t do any IF statements or any additional control and the reason is that the writing will still take some time, but not as much as writing any possible line one by one. Using this code you will reduced the file creating from 20 minutes to 4 minutes. That is 5 times less than before.

P.S. Using Oracle clob or blob types are still slower than the method above.

The last Oracle PL/SQL anonymous block is working in the same principle as the previous one with only one exception it is using a SQL query and the columns will be separated using coma.

DECLARE 
  w_file      UTL_FILE.FILE_TYPE;
  w_next_line VARCHAR2(1);
  w_text      VARCHAR2(32700);
  -- procedure to close the file
  PROCEDURE close_file IS
  BEGIN
    UTL_FILE.FCLOSE(w_file);
  EXCEPTION
    WHEN OTHERS THEN -- when the file never opened do not raise an error
         null;
  END;
  -- Write after the variable gets full
  PROCEDURE write_to_file(p_text VARCHAR2) IS  
  BEGIN
    w_text := w_text||w_next_line||p_text;
  EXCEPTION
    WHEN VALUE_ERROR THEN -- when the variable is full then write into file
     UTL_FILE.PUT_LINE(w_file,w_text);
     w_text := p_text;
  END;
BEGIN
  -- open file in writing mode and make it empty
  w_file := UTL_FILE.FOPEN('MY_DIR','onlinetechsupport.txt','W',32760);

  FOR rec IN (SELECT * FROM customers t )
  LOOP
    write_to_file(rec.ID ||',"'|| 
                  rec.FORENAME||'","'||
                  rec.SURNAME||'"');

    -- do not add an empty line in the beginning of file
    w_next_line := CHR(10);
  END LOOP;
  -- write the last text to complete file
  UTL_FILE.PUT_LINE(w_file,w_text);
  -- close file
  close_file;
EXCEPTION
    WHEN OTHERS THEN
         close_file; -- on error close the file
         raise; -- raise the error
END;

See Also:
Home