13. PLVfile: Reading and Writing Operating System Files
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:
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:
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" ).
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.