Oracle PL/SQL Programming, 5th Edition

Reading and Writing Files

The UTL_FILE package allows PL/SQL programs to both read from and write to any operating-system files that are accessible from the server on which your database instance is running. You can load data from files directly into database tables while applying the full power and flexibility of PL/SQL programming. You can generate reports directly from within PL/SQL without worrying about the maximum buffer restrictions of DBMS_OUTPUT that existed prior to Oracle Database 10g Release 2.

UTL_FILE lets you read and write files accessible from the server on which your database is running. Sounds dangerous, eh? An ill-intentioned or careless programmer could theoretically use UTL_FILE to write over tablespace datafiles, control files, and so on. Oracle allows the DBA to place restrictions on where you can read and write your files in one of two ways:

• UTL_FILE reads and writes files in directories that are specified by the UTL_FILE_DIR parameter in the database initialization file.

• UTL_FILE also reads/writes files in locations specified by database “Directory” objects.

After explaining how to use these two approaches; I will examine the specific capabilities of the UTL_FILE package. Many of the UTL_FILE programs are demonstrated in a handy encapsulation package found in the fileIO.pkg file on the book’s web site.

The UTL_FILE_DIR Parameter

Although not officially deprecated, the UTL_FILE_DIR approach is rarely used with the latest versions of the Oracle database. Using ...