The UTL_FILE package is an essential element for any PL/SQL developer. Oracle saw the need to implement a package that could read and write data to the file system in the 1990s. Oracle introduced the UTL_FILE package in Oracle 7 to meet that requirement.

This article shows you how to effectively use the UTL_FILE package and recommends when you should avoid it. This article explains the how, what, and why associated with the UTL_FILE package. Let me summarize the best practices this article recommends:

  • You use the UTL_FILE package when:
    • You want to write structured data from the database
    • You can’t implement an external table to read structured data into the database
    • You want to remove a file from the file system
  • You shouldn’t use the UTL_FILE package when:
    • You want to copy or move a file
    • You want to read or write a large object (LOB) file

The article is divided into three parts. They are:

  • How to use the UTL_FILE package
  • How to use the UTL_FILE package to write structured data
  • When you shouldn’t use the UTL_FILE package

If you’re familiar with the UTL_FILE package, skipping to what you need to know should be fine but otherwise please read this article from start to finish. Just one word of caution to those who opt to skip the first section, the traditional approach most developers and examples on the web use isn’t the correct way to use the UTL_FILE package to read files.

How to use the UTL_FILE package

The UTL_FILE package’s delivery in Oracle 7 presented a security vulnerability, which required configuration of the utl_file parameter in the init.ora file, which was a clear text file. The first fix to this moved the contents of the init.ora file to the spfile.ora file because the parameters values weren’t readable from the operating system. That’s because you set the spfile.ora file through internal Oracle SQL commands.

Oracle improved the security of the UTL_FILE package by replacing the utl_file parameter with a virtual directory. If you’re working in an unsupported version of the Oracle database, which happens from time to time, you may need to use the utl_file parameter. You set the utl_file parameter with the following syntax:

SQL> ALTER SYSTEM

  2  SET utl_file_dir='/u01/app/oracle/upload'

  3  SCOPE=SPFILE;

It’s very important that you understand who owns a physical directory when you assign it to the utl_file parameter. The oracle user must be the operating system owner of the physical directory.  The directory also must share the same primary group of the oracle user. If you forget this, the UTL_FILE package can’t access the directory.

As explained, virtual directories replace assigning values to the utl_file parameter. If you’re unfamiliar with virtual directories, let me introduce you to them. Virtual directories are an important feature of Oracle database. They let you define directory names inside the Oracle database. Virtual directories point to physical directories on the local operating system, and act like synonyms.

You create an entry in the database catalog when you create a directory inside the Oracle database. The entry in the database catalog maps a virtual directory name to a physical directory on the operating system. It is possible to have many virtual directories that point to the same physical directory, but you can’t point one virtual directory to more than one physical directory.

You define a virtual directory in SQL as the system user like

SQL> CREATE DIRECTORY importer

  2  AS '/u01/app/oracle/upload';

After you create a virtual directory, you need to assign privileges to it. You can assign read and write privileges for the sample user to the importer virtual directory with this syntax:

SQL> GRANT READ, WRITE ON DIRECTORY sample TO importer;

While the prior syntax grants read and write privileges on the external directory, sometimes you only want to grant read to one schema and write to another. The decision aligns itself

SQL> COLUMN grantee FORMAT A12

SQL> COLUMN privilege FORMAT A12

SQL> COLUMN table_name FORMAT A12

SQL> SELECT grantee

  2  ,      privilege

  3  ,      table_name

  4  FROM   dba_tab_privs

  5  WHERE  table_name = 'UPLOAD'

  6  AND    grantee = 'STUDENT'

It should return:

GRANTEE      PRIVILEGE      TABLE_NAME

------------ ------------ ------------

STUDENT      READ         UPLOAD

STUDENT      WRITE        UPLOAD

The typical way to read files is one row at a time until an error occurs. The following shows you to implement that approach.

SQL> DECLARE

  2    /* Local input variables. */

  3    lv_location  VARCHAR2(80) := 'UPLOAD';

  4    lv_filename  VARCHAR2(40) := 'test.sql';

  5 

  6    /* File reference pointer and buffer. */

  7    lv_file UTL_FILE.FILE_TYPE;

  8    lv_line VARCHAR2(32767);

  9  BEGIN

 10    /* Check for and close an open file. */

 11    IF utl_file.is_open(lv_file) THEN

 12        utl_file.fclose(lv_file);

 13    END IF;

 14 

 15    /* Managed exit by reading beyond the file. */

 16    LOOP

 17        /* Open file for reading. */

 18        lv_file := utl_file.fopen(

 19            location    => lv_location

 20          , filename    => lv_filename

 21          , open_mode   => 'r'

 22          , max_linesize => 32767);

 23 

 24        /* Read lines until none are found. */

 25        utl_file.get_line( file => lv_file

 26                    , buffer => lv_line

 27                    , len => 32767 );

 28 

 29        /* Print each line. */

 30        dbms_output.put_line(NVL(lv_line,CHR(10)));

 31        EXIT;

 32    END LOOP;

 33 

 34  EXCEPTION

 35 

 36    /* Close file after reading past last line. */

 37    WHEN NO_DATA_FOUND THEN

 38        utl_file.fclose(lv_file);

 39    /* Close file after read error. */

 40    WHEN UTL_FILE.READ_ERROR THEN

 41        dbms_output.put_line(

 42          'Position ['||utl_file.fgetpos(lv_file)||']');

 43        utl_file.fclose(lv_file);

 44  END;

 45  /

If there is a configuration error and you can’t access the file, you raise an exception by calling the FOPEN function on lines 18 through 22. The GET_LINE procedure call on lines 25 through 27 raises an exception when you read beyond the end of the file.

You should notice that each exception handler requires an explicit FCLOSE procedure call. The FCLOSE procedure call shouldn’t be in the EXCEPTION block. There should only be one FCLOSE procedure call, and it should really be in the execution block.

The next example fixes the problems by calling the FGETATTR procedure before attempting to open or read the file. The FGETATTR procedure lets you determine the file size and read the file up to the last character before the end of file marker. This program locates all the logic inside the execution block, and only runs exception handling in the exception block.

SQL> DECLARE

  2    /* Local input variables. */

  3    lv_location  VARCHAR2(80) := 'UPLOAD';

  4    lv_filename  VARCHAR2(40) := 'test.sql';

  5 

  6    /* File reference pointer and buffer. */

  7    lv_file      UTL_FILE.FILE_TYPE;

  8    lv_line      VARCHAR2(32767);

  9 

 10    /* Declare local size variables. */

 11    lv_file_size  NUMBER;

 12    lv_line_size  NUMBER;

 13    lv_read_size  NUMBER := 0;

 14 

 15    /* Declare local file attribute data. */

 16    lv_file_exists  BOOLEAN := FALSE;

 17    lv_block_size   BINARY_INTEGER;

 18  BEGIN

 19    /* Check for and close an open file. */

 20    IF utl_file.is_open(lv_file) THEN

 21        utl_file.fclose(lv_file);

 22    END IF;

 23 

 24    /* Read file attributes to get physical file size. */

 25    utl_file.fgetattr(

 26          location    => lv_location

 27        , filename    => lv_filename

 28        , fexists => lv_file_exists

 29        , file_length => lv_file_size

 30        , block_size  => lv_block_size );

 31 

 32    /* Open only files that exist. */

 33    IF lv_file_exists THEN

 34 

 35        /* Open file for reading. */

 36        lv_file := utl_file.fopen(

 37            location    => lv_location

 38          , filename    => lv_filename

 39          , open_mode   => 'r'

 40          , max_linesize => 32767);

 41 

 42        /* Read lines until none are found. */

 43        WHILE (lv_read_size < lv_file_size) LOOP

 44          utl_file.get_line( file => lv_file

 45                      , buffer => lv_line

 46                      , len => 32767 );

 47 

 48          /* Print each line. */

 49          dbms_output.put_line(NVL(lv_line,CHR(10)));

 50 

 51          /* Add the line size to the read size. */

 52          lv_read_size := lv_read_size

 53                   + LENGTH(NVL(lv_line,CHR(10))) + 2;

 54        END LOOP;

 55 

 56        /* Close the file. */

 57        utl_file.fclose(lv_file);

 58    END IF;

 59  EXCEPTION

 60    /* Close file after read error. */

 61    WHEN UTL_FILE.READ_ERROR THEN

 62        dbms_output.put_line(

 63          'Position ['||utl_file.fgetpos(lv_file)||']');

 64  END;

 65  /

There are three keys to fixing the problem. On lines 25 through 30 you collect the file details, and then you check whether the FGETATTR procedure returns a true value through the FEXISTS parameter. If the value is true you read the file within the boundaries provided by the other OUT mode parameters. Lines 43 through 54 manage the file reading process. On line 57 you close the open file as the last step of the execution block.

You should now know how to use the UTL_FILE package to read files. Though that’s not really the best practice. The next section shows you how to use the UTL_FILE package to write structured data.

How to use the UTL_FILE package to write structured data

Using the UTL_FILE package to write structured data to the file system is still an effective solution and the primary remaining use case for the package. The following shows you how to read data from a cursor and write it to an external file.

SQL> DECLARE

  2    /* Local input variables. */

  3    lv_location  VARCHAR2(80) := 'UPLOAD';

  4    lv_filename  VARCHAR2(40) := 'test.sql';

  5 

  6    /* File reference pointer and buffer. */

  7    lv_file      UTL_FILE.FILE_TYPE;

  8    lv_line      VARCHAR2(32767);

  9 

 10    /* Local variable for content to write to file. */

 11    lv_source  CLOB := '';

 12 

 13    /* Declare local size variables. */

 14    lv_file_size  NUMBER;

 15    lv_line_size  NUMBER;

 16    lv_read_size  NUMBER := 0;

 17 

 18    /* Declare local file attribute data. */

 19    lv_file_exists  BOOLEAN := FALSE;

 20    lv_block_size   BINARY_INTEGER;

 21 

 22    /* Declare a static cursor. */

 23    CURSOR get_items IS

 24        SELECT   i.item_title

 25        ,     i.item_subtitle

 26        FROM       item i;

 27  BEGIN

 28    /* Check for and close an open file. */

 29    IF utl_file.is_open(lv_file) THEN

 30        utl_file.fclose(lv_file);

 31    END IF;

 32 

 33    /* Read file attributes to get physical file size. */

 34    utl_file.fgetattr(

 35          location    => lv_location

 36        , filename    => lv_filename

 37        , fexists => lv_file_exists

 38        , file_length => lv_file_size

 39        , block_size  => lv_block_size );

 40 

 41    /* Remove files that exist. */

 42    IF lv_file_exists THEN

 43        utl_file.fremove( location => lv_location

 44                        , filename => lv_filename );

 45    END IF;

 46 

 47    /* Open file for reading. */

 48    lv_file := utl_file.fopen(

 49          location    => lv_location

 50        , filename    => lv_filename

 51        , open_mode   => 'w'

 52        , max_linesize => 32767);

 53 

 54    /* Open cursor and write output to CSV. */

 55    FOR i IN get_items LOOP

 56        /* Assign columns to CSV line. */

 57        lv_source := i.item_title || ',' || i.item_subtitle;

 58 

 59        /* Write all lines to buffer. */

 60        utl_file.put_line( file   => lv_file

 61                  , buffer => lv_source );

 62 

 63        /* Flush buffer to file. */

 64        utl_file.fflush( file => lv_file );

 65    END LOOP;

 66 

 67    /* Close the file. */

 68    utl_file.fclose(lv_file);

 69  EXCEPTION

 70    /* Close file after read error. */

 71    WHEN UTL_FILE.READ_ERROR THEN

 72        dbms_output.put_line(

 73          'Position ['||utl_file.fgetpos(lv_file)||']');

 74  END;

 75  /

While use the FGETATTR in this program too, it’s for a different reason. Here you use it to determine if a file exists. The test block on lines 42 through 45 removes a file when it exists. Naturally, you’d have a different behavior if you were adding to a file, which the UTL_FILE package calls appending to a file.

You open and read the cursor contents into a comma delimited value (CSV) line in the loop process on lines 55 through 65. Line 57 concatenates the SELECT list elements with commas. Line 60 and 61 write the CSV line to a buffer, and line 64 flushes the buffer to file. Like the prior best practice, you close the file as the last part of the execution block on line 68.

When you shouldn’t use the UTL_FILE package

There are three use cases that are possible with UTL_FILE but not recommended. There not recommended because subsequent releases of the Oracle database have given you better solutions.

You shouldn’t use the UTL_FILE package:

  • To read structured data into the database. That’s because external tables are a more effective solution. You can read more about how to implement external tables in this earlier article of mine.
  • To read large objects into the database. That’s because the DBMS_LOB package does it more efficiently.
  • To write large objects from the database to physical files. That’s because the DBMS_LOB package gives you more options to write and append to files, and it’s more efficient.

This article should have shown you how to use the UTL_FILE package and understand where and when to use it.