Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle utl_file Package

Oracle Database Tips by Donald Burleson

About Oracle utl_file Package

This easy writing to the alert log is accomplished by using Oracle's utl_file package. The Oracle utl_file package allows Oracle SQL and PL/SQL to read and write directly from flat files on the server.

Writing custom messages to the Oracle alert log requires the following steps:

1 - Locate the background dump directory (the location of the alert log).
2 - Set the utl_file_dir initialization parameter.
3 - Execute utl_file.fopen to open the file for write access.
4 - Use dbms_output.put_line to write the custom message to the alert log.
5 - Execute utl_file.fclose to close the file

-- ******************************************************
-- Gather the location of the alert log directory
-- ******************************************************

name into :alert_loc
name = 'background_dump_destination';

-- ******************************************************
-- Set the utl_file_dir
-- (prior to Oracle9i, you must bounce the database)
-- ******************************************************
alter system set utl_file_dir = ':alert_log');

-- ******************************************************
-- Open the alert log file for write access
-- ******************************************************

-- ******************************************************
-- Write the custom message to the alert log file
-- ******************************************************

-- ******************************************************
-- Close the alert log file
-- ******************************************************

Oracle lets a database program write to flat files using the utl_file utility


(Directory of files created using the utl_file package)

The user interaction directory is placed here. The files created using the package utl_file are generated in this directory. Since users will frequently read and write files in this directory, this should be separated. The directory should be owned by Oracle with permissions set for read, write and execute for everyone.

Please note then in Oracle 9i, the utl_file package does not need this parameter to be set in init.ora to manipulate a file. The DIRECTORY object can be dynamically created and used as the directory. Therefore, the days of this filesystem could be numbered.

If the developer has done any file processing (reading or writing on the file system), he is certain to already be familiar with UTL_FILE. This package is similar to how C or C++ reads/writes files on a file system. The general process is to set a pointer to a file's location and name, open it, manipulate the contents, then close it.

Oracle provides an extensive set or list of exceptions which can occur when dealing with files on the operating system. If it happens that the developer is using UTL_FILE in several places, it would be worthwhile to bundle these exceptions into their own package to support code reuse.

Starting with Oracle 10g, an enhancement to UTL_FILE is the package's ability to write out the contents of a stored BLOB to the file system. Put another way, a stored JPEG file can be output. Aside from having the appropriate permissions, one may have to account for the file size and write the contents out in PL/SQL variable-sized chunks of 32KB at a time.

Another enhancement is the departure from having the location(s) specified by utl_file_dir in the parameter file. Previously, more than one location could be specified using this parameter as long as the locations all appeared together, meaning no other parameters in between two locations. The latest recommendation is to use a directory object.

A directory location is identified to Oracle and stored within the database. Users are then granted read or write on the directory. This allows for multiple locations and more granular control of where a user can read/write. Query the dba_directories data dictionary view for a description of all directories available to the user running the query.

As an example, take the Word document inserted as a BLOB (back in the UTL_COMPRESS section) and write back out to the file system. If the script was ran through as is, there will be a BLOB stored in the record where INDX=1 or the filename can be used. Here is an example of a procedure to write a BLOB to the MYDIR directory.



(infilename IN VARCHAR2) IS


  v_blob         BLOB;

  blob_length    INTEGER;

  out_file       UTL_FILE.FILE_TYPE;

  v_buffer       RAW(32767);

  chunk_size     BINARY_INTEGER := 32767;

  blob_position  INTEGER := 1;




  -- Retrieve the BLOB for reading

  -- This uses a Word document

  SELECT y INTO v_blob FROM compress_blob WHERE indx = 1;


  -- Retrieve the SIZE of the BLOB



  -- Open a handle to the location of the BLOB file

  -- The location is the MYDIR directory

  -- wb = write in byte mode, 10g new feature

  -- The out_file picks up the name of the filename passed in

  out_file := UTL_FILE.FOPEN

    ('MYDIR', infilename, 'wb', chunk_size);


  -- Write the BLOB to file in chunks

  WHILE blob_position <= blob_length LOOP

    IF blob_position + chunk_size - 1 > blob_length THEN

      chunk_size := blob_length - blob_position + 1;

    END IF;

    DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer);

    UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);

    blob_position := blob_position + chunk_size;



  -- Close the file handle

  UTL_FILE.FCLOSE (out_file);




Compiling this procedure and executing it…

SQL> exec writeblobtofile('A_57KB_Word_doc.doc');


PL/SQL procedure successfully completed.

 …writes the "A_57KB_Word_doc.doc" file back into C:\Temp. The ability to write files out like this must be safeguarded. Imagine the damage a malicious - or not - user can wreak by being able to what amounts to download from the database any document.

The UTL_FILE package contains an amazing degree of potential. The FREMOVE and FRENAME procedures do exactly what their names imply. So just as potentially dangerous as writing files out to the file system, misuse of these two procedures, inadvertent or otherwise, can be disastrous. Imagine someone playing "what if" with database files. "I wonder if I can read, write, remove, or rename a database file with UTL_FILE?" Clearly, one does not want users being able to create directory objects on database file locations.



This package was introduced in the Oracle version 7.3.4 for reading and writing any operating system flat file that is accessible in our database server. This new feature allowed us to write the flat file data into the database tables along with the flexibility of PL/SQL with an ease.


The different objects available in this package are described below.


Prior to the Oracle release 9i, the UTL_FILE_DIR initialization parameter was used for determining the directories which can be read or written by the UTL_FILE package. This became a security threat as the directories present inside this parameter are accessible by any other database user. To mitigate this security concern, Oracle has introduced the CREATE DIRECTORY syntax for creating directories with more security for working alongside the OS files. Either read or write access to the directories created using this syntax can be granted to individual users or roles.


The prototype for creating a directory is shown below,


CREATE DIRECTORY <Directory_name> AS '<Directory_path>';


The prototype for granting access to the directories to the database users is shown below,


GRANT [READ] | [WRITE] ON DIRECTORY <Directory_name> TO <Database_user>;

FILE_TYPE Record Type

This type is actually a PL/SQL record type which is used for holding all the information related to the file for processing by the UTL_FILE package. This type acts as a handle for the consecutive calls to the UTL_FILE package to manipulate the file content. We must not reference the individual attributes of this type or manipulate them as it is specifically to be handled by the UTL_FILE package.


The prototype of the FILE_TYPE record type is shown below,


TYPE file_type IS RECORD (

   id          BINARY_INTEGER,

   datatype    BINARY_INTEGER,

   byte_mode   BOOLEAN);


·         ID parameter returns a numeric file handler number which is generated internally for processing.

·         DATATYPE parameter indicates the data type of the file.

·         BYTE_MODE parameter is to indicate whether the file is open as a text file or as a binary file.

Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


Also see these related notes on Oracle utl_file:

Oracle utl_file server directory vulnerability discovered

Oracle Table Design utl_file External Tables

Incorrect utl_file permission umask tips




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.