Advanced Oracle PL/SQL Programming with Packages

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