Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 12.9 Cleaning Up Source and TargetChapter 13Next: 13.2 Specifying the File in PLVfile

13. PLVfile: Reading and Writing Operating System Files

A Review of UTL_FILE
Specifying the File in PLVfile
Creating and Checking Existence of Files
Opening and Closing Files
Reading From a File
Writing to a File
Copying File Contents
Displaying File Contents
Handling File Errors with PLVfile
Tracing PLVfile Activity

The PLVfile (PL/Vision FILE package) provides a layer of code around the builtin UTL_FILE package (which is available only with Release 2.3 of PL/SQL and beyond). UTL_FILE allows you to read from and write to operating system files on the same machine in which the database instance is running. The ability to read and write operating system files has been a long-standing request ("desperate plea" would, perhaps, be a better description) of PL/SQL developers.

The PLVfile package provides a number of high-level programs, such as fcopy to copy files, and infile, a file-oriented version of INSTR, to make it easier for PL/SQL developers to take advantage of this very useful builtin package.

This chapter show how to use each of the different elements of the PLVfile package.

13.1 A Review of UTL_FILE

Before you dive in to using either UTL_FILE or the PLVfile package, however, you should review the following information about UTL_FILE. Chapter 15 of Oracle PL/SQL Programming offers more detail about these topics and the programs of the UTL_FILE package. The following sections offer some information about UTL_FILE that you need to know in order to use PLVfile properly.

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:


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:

   file_handle UTL_FILE.FILE_TYPE;

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

   file_handle UTL_FILE.FILE_TYPE;
   file_handle := PLVfile.fopen ('login.sql', PLVfile.c_read);
   p.l (;

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:

   (location_in IN VARCHAR2, file_name_in IN VARCHAR2,
    file_mode_in IN VARCHAR2)

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:

      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



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.


An internal error occurred. The requested operation was not completed.


The specified file handle does not identify a valid, open file. This exception may be raised by calls to FCLOSE and FFLUSH.


The mode supplied to FOPEN is not valid. Valid modes are: `a', `r', or `w' (upper or lower case is acceptable).


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.


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.


An operating system-specific error occurred when you tried to read from the file. For example, there might be a disk error.


An operating system-specific error occurred when you tried to write to the file. For example, the disk might be full.


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

Previous: 12.9 Cleaning Up Source and TargetAdvanced Oracle PL/SQL Programming with PackagesNext: 13.2 Specifying the File in PLVfile
12.9 Cleaning Up Source and TargetBook Index13.2 Specifying the File in PLVfile

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference