Catching Up on Filing

Oracle9i Release 2 UTL_FILE enhances file I/O.

September/October 2002

Some would argue that you can contain and represent the entire world, perhaps universe, in an Oracle database. That may be true, but there are those of us who still want to be able to work with operating system (OS) files from within our PL/SQL programs. Given that desire, developers have long had a love-hate relationship with the UTL_FILE supplied package

For years, UTL_FILE provided a way to read and write files from within PL/SQL. Some in the development community, however, have wanted to do more than read and write lines sequentially in files. Some developers wanted to delete a file, rename a file, copy a file, and perform other typical file-related operations, but UTL_FILE didn't support these operations.

Oracle9i Release 2 lifts those troublesome UTL_FILE restrictions. This article explores several new and very useful additions to the UTL_FILE package in Oracle9i Release 2, including:

• UTL_FILE.FREMOVE Remove a file.

• UTL_FILE.FRENAME Rename a file, and also, in effect, move files.

• UTL_FILE.FCOPY Copy all or part of one file to another.

• UTL_FILE.FGETATTR Retrieve attributes of the file, such as its length.

In addition to new programs, UTL_FILE also now allows database-defined directory objects to specify the location of OS directories. While it's not possible to cover all of the new features of UTL_FILE, you'll certainly find enough in this article to get you excited about new possibilities for file I/O within native PL/SQL code.

Before Oracle9i Release 2, UTL_FILE required that you explicitly name the location of the file when you called FOPEN to open the file (for read or write). This was a less-than-optimal implementation, because it usually meant that developers would hard-code those locations in multiple places in the application. If that directory changed, you had a messy clean-up job.

In Oracle9i Release 2, you can now specify the name of an Oracle directory object for the file system location of the directory. This technique "hides" the actual operating system location. If that location ever needs to change, you need to update only the directory object definition; all calls to FOPEN, FREMOVE, FRENAME, and FCOPY are unaffected.

To create a directory object, you will need the CREATE ANY DIRECTORY privilege. You then define a new directory object, as shown in this example:

CREATE OR REPLACE DIRECTORY DEVELOPMENT_
   DIR as '/dev/source';
CREATE OR REPLACE DIRECTORY TEST_DIR
   as '/test/source';

You should know that Oracle Database does not validate the location you specify when you create a directory object. Another thing to keep in mind is that when you specify the name of a directory object in a call to, say, UTL_FILE.FOPEN, it is treated as a case-sensitive string. In other words, if you do not specify the directory object name in uppercase, the operation will fail.

After creating the directory object, you can grant authority to specific users to work with that directory object as follows:

GRANT READ ON DIRECTORY DEVELOPMENT_DIR
   to senior_developer;

Finally, you can query the contents of ALL_DIRECTORIES to determine which directory objects are available in the currently connected schema. Here is a Boolean function that tells you if the specified directory object is available to you:

CREATE OR REPLACE FUNCTION dir_available (
   dir_in       IN   VARCHAR2,
   uppercase_in  IN   BOOLEAN := TRUE
)
   RETURN BOOLEAN
IS
   v_dir    VARCHAR2 (100) := dir_in;
   retval   BOOLEAN;
   dummy    CHAR (1);
BEGIN
   IF uppercase_in
   THEN
      v_dir := UPPER (v_dir);
   END IF;
   SELECT 'x'
     INTO dummy
     FROM all_directories
    WHERE directory_name = l_dir;
   RETURN TRUE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN FALSE;
END;

One advantage to building utilities like this one is that you can easily add sophisticated handling of the case of the directory to avoid formatting errors, such as forgetting to specify the directory name in uppercase.

In the bad old days, the only way to copy a file using UTL_FILE was to write a lot of code to read the content of one file (line by line) and then write it to a new file (line by line). Now, you simply ask UTL_FILE to do the work for you. In Listing 1, I use UTL_FILE.FCOPY to perform a selective backup—a copy of a single file from the development directory to the archive directory.

DECLARE
   file_suffix   VARCHAR2 (100)
          := TO_CHAR (SYSDATE, 'YYYYMMDDHHMISS');
BEGIN
   - Copy the entire file...
   UTL_FILE.fcopy (
      src_location      => 'DEVELOPMENT_DIR',
      src_filename      => 'archive.zip',
      dest_location     => 'ARCHIVE_DIR',
      dest_filename     =>    'archive'
                           || file_suffix
                           || '.zip'
   );
END;

You can also use FCOPY to copy just a portion of a file. To do this, you indicate the starting and ending line numbers of the file you want to copy. Suppose that I have a text file containing the names of the annual winners of my son's bowling league championship. I started recording the names in 1990 and would like to move all the names prior to 1996 into another file. I can do that by taking advantage of the fifth and sixth arguments of the following procedure:

- Copy just a part of a file
UTL_FILE.fcopy (
   src_location      => 'WINNERS_DIR',
   src_filename      => 'names.txt',
   dest_location     => 'OLD_NEWS_DIR',
   dest_filename     => 'prevnames.txt',
   start_line        => 1,
   end_line          => 6
   );

Deleting a file is also now a breeze. Suppose that after moving my zip file to the archive directory, I would like to remove it to free up some disk space. This is the perfect opportunity to deploy UTL_FILE.FREMOVE. Notice that in Listing 2, I also include an explicit exception handler for the new UTL_FILE.DELETE_FAILED exception. This approach allows me to flag a delete failure (due, for example, to the fact that I do not have the necessary privileges).

BEGIN
   UTL_FILE.fremove (
      src_location     => 'DEVELOPMENT_DIR',
      src_filename     => 'archive.zip'
   );
EXCEPTION
   - If you call FREMOVE, you should check explicitly
   - for deletion failures.
   WHEN UTL_FILE.delete_failed
   THEN
      ... Deal with failure to remove
END;

I can also combine copy and remove operations into a single step by calling the UTL_FILE.FRENAME procedure. This handy utility allows me to either rename a file in the same directory or rename a file to another name and location (in effect, moving that file). The example in Listing 3 uses FRENAME to move a file: archive.zip.

DECLARE
   file_suffix   VARCHAR2 (100)
            := TO_CHAR (SYSDATE, 'YYYYMMDD');
BEGIN
   - Rename/move the entire file in a single step.
   UTL_FILE.frename (
      src_location      => 'DEVELOPMENT_DIR',
      src_filename      => 'archive.zip',
      dest_location     => 'ARCHIVE_DIR',
      dest_filename     =>    'archive'
                           || file_suffix
                           || '.zip',
      overwrite         => FALSE
   );
EXCEPTION
   WHEN UTL_FILE.frename_failed
   THEN
      ... Deal with failure to rename
END;

Again, when working with FRENAME, you should include an exception handler that will trap very specifically a rename failure.

How big is this file? Does a specific file even exist? What is the block size of my file? Such questions are no longer mysteries to be solved only with the help of an OS command. UTL_FILE. FGETATTR now provides all that information in a single native procedure call. Perhaps the best way to take advantage of FGETATTR is to build your own function—on top of the built-in function—that answers a single question, such as the example in Listing 4 that returns a file's size (length).

CREATE OR REPLACE FUNCTION flength (
   location_in   IN   VARCHAR2,
   file_in       IN   VARCHAR2
)
   RETURN PLS_INTEGER
IS
   TYPE fgetattr_t  IS RECORD (
      fexists       BOOLEAN,
      file_length   PLS_INTEGER,
      block_size    PLS_INTEGER
   );
   fgetattr_rec   fgetattr_t;
BEGIN
   UTL_FILE.fgetattr (
      location         => location_in,
      filename         => file_in,
      fexists          => fgetattr_rec.fexists,
      file_length      => fgetattr_rec.file_length,
      block_size       => fgetattr_rec.block_size
   );
   RETURN fgetattr_rec.file_length;
END flength;
/

With the function in place, I can now very easily obtain the file size without having to declare variables for each of the attributes retrieved by FGETATTR, as in the following PL/SQL example:

how_big := flength ('DEVELOPMENT_DIR',
   'all_the_rules.pkg');

This FLENGTH function also includes code to find a file's block size and determine whether a file exists. You can easily apply the same technique that is used to find the file length (size)—shown in Listing 4—to create functions to obtain the block size and return a simple Boolean value to determine whether or not the file exists.

Another new capability in UTL_FILE is the "auto-flush" feature of PUT_LINE. When you write data out to a file in your program, it doesn't necessarily immediately appear in that file, ready to be read. Instead, the OS almost certainly utilizes asynchronous I/O, filling a buffer with the results of multiple write operations before actually sending those bits to disk.

While asynchronous I/O improves performance, it can be an irritation for programmers or support staff who need to see the contents of, say, a log file immediately.

UTL_FILE now includes a new parameter in the UTL_FILE.PUT_LINE procedure so that a developer can specify that she wants the line of text supplied to be flushed immediately to disk. The procedure in Listing 5 demonstrates this technique.

CREATE OR REPLACE PROCEDURE log_error (
   error_in     IN   PLS_INTEGER,
   message_in   IN   VARCHAR2 := NULL
)
IS
   v_handle              UTL_FILE.file_type;
BEGIN
   v_handle :=
      UTL_FILE.fopen (
         location          => 'ERROR_LOG_DIR',
         filename          => 'error.log',
         open_mode         => 'a',
         max_linesize      => 32767
      );
   UTL_FILE.put_line (
      FILE           => v_handle,
      buffer         =>    'Error '
                        || error_in
                        || ' occurred at '
                        || TO_CHAR (
                              SYSDATE,
                              'YYYYMMDD HH:MI:SS'
                           )
                        || ': '
                        || NVL (
                              message_in,
                              SQLERRM
                           ),
      autoflush      => TRUE
   );
   UTL_FILE.fclose (v_handle);
END; 

For PL/SQL to be used widely and successfully, it must strongly support a wide range of functionality. Interaction with OS files is certainly a key part of that range.

Oracle has once again responded to the requests of its developer community and improved core PL/SQL functionality. UTL_FILE for Oracle9i Release 2 now offers a significantly higher level of support for common operations.