Day 18

Writing to Files and the Display

by Timothy Atwood

By default, Oracle does not have any functional input and output processes when using PL/SQL. However, Oracle provides several packages that perform input and output. Today's lesson discusses the following packages:

• The UTL_FILE package
• The TEXT_IO package
• The DBMS_OUTPUT package

UTL_FILE Definition

The UTL_FILE package is a very powerful and flexible package that enables you to work with files on both the client side and server side; you can work with files on different operating systems. This can be a scary proposition on the server side unless proper security is in place. You can specify the only directories that can be written to by the UTL_FILE package with the utl_file_dir parameter located in the INIT.ORA Oracle initialization file. If you do not have this line in your initialization file, add the statement utl_file_dir = directory_name, where directory_name is the directory location where the files are stored.

You could then limit access to a temp directory on the server (assuming Personal Oracle) by specifying

INPUT
utl_file_dir = c:\temp

In addition, if you want to give write permission to all directories, you would simply specify

INPUT
utl_file_dir = *

When an Oracle user creates a file, all rights and permissions to the file are assigned to the user. If other users need access to those files, it is up to the security administrator to change the permissions on the files.

File Input

The UTL_FILE package enables you to read into Oracle files located on the server or on the client. You could easily convert ASCII to EBCDIC files between the client and the server, utilizing some of the Oracle functions you learned on Day 7, "Using Oracle's Built-in Functions," and in Appendix B, "Oracle Functions Reference." Other possibilities include

• Reading in files output from any application in text mode (ASCII or EBCDIC)
• Reading files from any operating system
• Reading files created by a scanner with OCR capabilities
• Inserting this data into a table or just viewing the data

As you can see, this gives you tremendous flexibility when exchanging files between platforms, applications, and so on with Oracle. The process to read in a file is

1. Assign a file handle to the file. This creates a pointer to the file to provide information to the operating system such as whether the file is in use, and where the user is currently located in the file for reading and writing operations.
2. Declare a string of type VARCHAR2 to act as a buffer for reading in the file one line at a time.
3. Use FOPEN to open the file in read mode, as specified by the parameter R.
4. Use GET_LINE to read in data from the file into your declared buffer of type VARCHAR2, one line at a time.
5. Close the file and release the file handle by using FCLOSE.

The next few sections examine the details of reading a file into Oracle.

Declaring a File Handle
When you open a file with the UTL_FILE package, a file handle is returned with a datatype of UTL_FILE.FILE_TYPE.

The Syntax for Creating a File Handle
The format to create the file handle is

DECLARE
     v_READFILE UTL_FILE.FILE_TYPE;
BEGIN

The FILE_TYPE is a PL/SQL record, which contains the necessary information about the file you are attempting to read or write including the filename, file location, method of accessing the file, and so on.

Using FOPEN to Open a File
FOPEN opens a file for reading or writing only. Table 18.1 provides a listing of possible exceptions raised from the function call. You cannot perform both operations on the same file simultaneously.

The Syntax for the FOPEN Function
FOPEN is defined as

FUNCTION FOPEN(location IN VARCHAR2,
                    filename IN VARCHAR2,
                    openmode IN VARCHAR2)
RETURN UTL_FILE.FILE_TYPE;

The first parameter location is a VARCHAR2 string, which holds the location (path) of the file. This can be restricted by utl_file_dir as mentioned earlier in this chapter. The second parameter, filename, is the actual filename itself. The third parameter, openmode, defines how you will access the file.

You can access the file as

• R—Read-only access.
• W—If the file does not exist, the file is created; otherwise, all information in the original file is deleted and replaced with the new contents you are writing.
• A—Allows you to append text to a file instead of overwriting the file with the parameter W. If the file does not exist, it will be created.

For file input, you will use only mode `R'. The function then returns the file handle of type UTL_FILE.FILE_HANDLE for subsequent use.

Table 18.1. Exceptions raised by FOPEN.

Using IS_OPEN to Test If a File Is Open
As part of error control, before you attempt to open a file, you can test to see if the file is already open by using IS_OPEN. You can also test to make sure a file is open before you attempt to close the file.