13.1.1 Enabling File Access in the Oracle Server

To use the UTL_FILE package, you must add a line to the initialization file or init.ora for your database instance that indicates the directories in which you can read and write operating system files. This precaution is taken by Oracle so that you do not inadvertently corrupt important files like the database log files.

The entry in the init.ora file can have one of two formats:

utl_file_dir='*'
or
utl_file_dir='dir1,dir2...dirn'

where dir1 through dirn are individual, specific directory listings. If you use the first format, you are telling the Oracle database that developers can use UTL_FILE to write to any directory.

13.1.2 File Handles

Before you can do anything with a file, you have to open it (this process is explained below). At this point, UTL_FILE returns a handle or pointer to that file. You will then use this handle in all future manipulations of the file. A file handle has a special datatype of UTL_FILE.FILE_TYPE. FILE_TYPE is actually a PL/SQL record whose fields contain all the information about the file needed by UTL_FILE. (Currently, the record consists of a single column, named "id".)

You will reference the file handle, but not any of the individual fields of the handle. A handle is declared as follows:

DECLARE
   file_handle UTL_FILE.FILE_TYPE;
BEGIN

You could display the file handle which is generated by a call to UTL_FILE.FOPEN or the corresponding PLVfile.fopen functions as follows:

DECLARE
   file_handle UTL_FILE.FILE_TYPE;
BEGIN
   file_handle := PLVfile.fopen ('login.sql', PLVfile.c_read);
   p.l (file_handle.id);
END;
/

The p.l procedure is also overloaded in the PL/SQL 2.3 version so you can pass it the file handle directly and it will display the id field, as shown here:

p.l (file_handle);

Many PLVfile programs give you the option of providing either the file name or the file handle. In some cases, such as when you read from a file, you must use the file handle. In other situations, you can choose your method of specifying the file you want.

13.1.3 File Location, Name, and Mode

When you open a file with the UTL_FILE.FOPEN function, you must provide three arguments, as shown in the header below:

FUNCTION FOPEN 
   (location_in IN VARCHAR2, file_name_in IN VARCHAR2,
    file_mode_in IN VARCHAR2)
RETURN UTL_FILE.FILE_TYPE;

The first argument is the location of the file (the directory); the second is the name of the file (name and extension); and the third is the file mode: "R" for read-only, "W" for write-only, and "A" for append.

While UTL_FILE needs all of this information, you should not necessarily have to provide it all every time you want to perform a file-related action. To make it easier for developers to work with files, PLVfile offers several options for opening and referencing files. You can provide separate locations and names in the UTL_FILE format. You can also provide a single string that which contains both the location and name and let PLVfile parse that string into its separate components.

See Section 13.2, "Specifying the File in PLVfile" for more information on the approach taken by PL/Vision.

13.1.4 Handling UTL_FILE Errors

The UTL_FILE package provides a set of package-based exceptions and also makes use of two, more generic exceptions to inform you of problems it encounters. These exceptions are shown in Table 13.1.

It is great that the UTL_FILE package offers some predefined exceptions. By providing specific names for different exception conditions, I can trap for and handle those conditions. The downside of this approach is that I need to include explicit exception handlers by name, as shown below:

EXCEPTION
   WHEN UTL_FILE.INVALID_PATH
   THEN
      p.l ('Invalid path');

If I try to use a WHEN OTHERS clause instead (as you can see, there are many UTL_FILE-specific exceptions), the SQLCODE function simply and uniformly returns the number 1 -- indicating a user-defined exception. I cannot, in other words, determine which of the UTL_FILE exceptions occurred.

To help you deal with this situation, PLVfile offers the exc_section procedure, which predefines all these handlers (see Section 13.9, "Handling File Errors with PLVfile").