The Syntax for the IS_OPEN Function
The format for the function IS_OPEN is

FUNCTION IS_OPEN(file_handle IN UTL_FILE.FILE_TYPE)
     RETURN BOOLEAN;

If the file is open, the value true is returned; otherwise, false is returned. file_handle is the file handle returned by FOPEN.

Using GET_LINE for File Input
When performing file input, in order to read data from the file into the buffer, you'll use the GET_LINE function. The maximum input length is 1022 bytes.

The Syntax for the GET_LINE Function
The format for the function GET_LINE is

PROCEDURE GET_LINE(file_handle IN UTL_FILE.FILE_TYPE,
     buffer OUT VARCHAR2);

The first parameter, file_handle, is the file handle returned from the FOPEN function. The second parameter, buffer, is the buffer of type VARCHAR2. Possible errors that could arise are shown in Table 18.2.

Table 18.2. Exceptions raised by GET_LINE.

                                                 continues

Table 18.2. continued

File Output

Because you can input files, you should be able to output files. As you will see, this is a great method for importing and exporting files from one application, operating system, and so on to another. It works well in mixed IBM, UNIX, and PC shops. The steps to output to a file are as follows:

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. Use FOPEN to open the file to replace or append text. FOPEN returns the file handle associated with the open file.
3. Use PUT, PUTF, PUT_LINE, NEW_LINE, or FFLUSH to write to the file.
4. Close the file and release the file handle by using FCLOSE.

The next few sections look at the detailed process of writing to a file into Oracle.

Opening a File for Output
You will open the file in the same manner for output as you did for input. The only difference is that the mode of operations will be REPLACE (`W') or APPEND (`A').

The Syntax for Opening a File for Output
The syntax again is

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

All the same exceptions apply to opening the file for writing as they did for reading
from a file.

Using IS_OPEN to Test If a File Is Open When Writing
to a File
You should test to make sure the file is open before writing to the file. This can be accomplished through properly handling the raised exceptions.

The Syntax for Testing to Make Sure a File Is Open
The syntax is still the same for both reading and writing to files:

FUNCTION IS_OPEN(file_handle IN UTL_FILE.FILE_TYPE)
     RETURN BOOLEAN;

If the file is open, the value true is returned; otherwise, false is returned. file_handle is the file handle returned by FOPEN.

Using PUT to Write to the Output File
The PUT procedure outputs the string to the output file. This assumes that the file has been opened for writing.

The Syntax for the PUT Procedure
The format for the PUT procedure is

PROCEDURE PUT(file_handle IN UTL_FILE.FILE_TYPE,
     buffer IN VARCHAR2);

The file_handle parameter is the file handle returned from FOPEN. The buffer parameter is the output to the file. The maximum length of the buffer is 1023. Table 18.3 lists possible exceptions and their meanings.

Table 18.3. Exceptions raised by PUT, PUT_LINE, PUTF, and FFLUSH.

Using the NEW_LINE Procedure
The NEW_LINE procedure outputs one or more newline characters in the format specified by the operating system. For instance, ASCII is `0D0A' and EBCDIC is `0D25' for the newline character. This procedure would be used to add a newline character in conjunction with the PUT statement.

The Syntax for the NEW_LINE Procedure

PROCEDURE NEW_LINE(file_handle IN UTL_FILE.FILE_TYPE,
     no_newline_output IN NATURAL :=1);

The file_handle parameter is the file handle returned from FOPEN. no_newline_output is the total number of newline characters you want to write to the file. The default is one newline character if not specified. The same exceptions are raised as with PUT, so you can refer to Table 18.3 for a brief review.

Using the PUT_LINE Procedure
The PUT_LINE procedure outputs a string to a file, followed by the platform-specific newline character. Basically it combines PUT and NEW_LINE into one procedure. Again, it is assumed that the file has been opened before you attempt to write to the file.

The Syntax for the PUT_LINE Procedure

PROCEDURE PUT_LINE(file_handle IN UTL_FILE.FILE_TYPE,
     buffer IN VARCHAR2);

The file_handle parameter is the file handle returned from FOPEN. buffer is your output string, which will be terminated by the newline character associated with the operating system. The same exceptions are raised as with PUT, so you can refer to Table 18.3 for a brief review.