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...dir
n
'

where dir1 through dir n 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.

Table 13.1: Exceptions Related to the UTL_FILE Package

Exception Name	Description
NO_DATA_FOUND	The GET_LINE procedure tried to read past the end of the file. Remember that this same exception is also raised by implicit cursors and references to PL/SQL tables.
UTL_FILE.INTERNAL_ERROR	An internal error occurred. The requested operation was not completed.
UTL_FILE.INVALID_FILE_HANDLE	The specified file handle does not identify a valid, open file. This exception may be raised by calls to FCLOSE and FFLUSH.
UTL_FILE.INVALID_MODE	The mode supplied to FOPEN is not valid. Valid modes are: `a', `r', or `w' (upper or lower case is acceptable).
UTL_FILE.INVALID_OPERATION	In FOPEN, this exception is raised when the file cannot be opened as requested. To open a file in read or append mode, the file must exist already. To open in write mode, the file must be writeable/ createable. In GET_LINE, FFLUSH, NEW_LINE, PUT, PUTF, and PUT_LINE, this exception is raised when you try to perform an operation which is incompatible with the mode under which the file was opened. For example, you tried to write to a read-only file.
UTL_FILE.INVALID_PATH	The path name supplied in a call to FOPEN is not valid. This error occurs when the location is not accessible or the path name is improperly constructed.
UTL_FILE.READ_ERROR	An operating system-specific error occurred when you tried to read from the file. For example, there might be a disk error.
UTL_FILE.WRITE_ERROR	An operating system-specific error occurred when you tried to write to the file. For example, the disk might be full.
VALUE_ERROR	The text read by GET_LINE is too long to fit in the specified buffer.

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" ).