Using the PUTF Procedure
The PUTF procedure is the most flexible for output to a file. This procedure operates similarly to the printf() function in C. You can output text and up to five sets of special string data, represented by `%s'. In addition, you can have as many newline characters as required, represented by the `\n'.

The Syntax for the PUTF Procedure

PROCEDURE PUTF(file_handle IN UTL_FILE.FILE_TYPE,
     formatted_line IN VARCHAR2,
     <string_item1 IN VARCHAR2 DEFAULT NULL,
     <string_item2 IN VARCHAR2 DEFAULT NULL,
     <string_item3 IN VARCHAR2 DEFAULT NULL,
     <string_item4 IN VARCHAR2 DEFAULT NULL,
     <string_item5 IN VARCHAR2 DEFAULT NULL,);

The file_handle parameter is the file handle returned from FOPEN. formatted_line is simply the line of text, which can optionally contain up to five `%s' for special strings of characters, and optionally as many newline characters (`\n') as desired (up to a maximum of 1023 bytes).

Comparison of PUT, PUTF, and PUT_LINE
Listing 18.1 shows the PL/SQL code that will output to a file the same exact text using the three different output procedures. Even though all three procedures accomplish the same exact output to the file, you will be able to see the different methods of using these procedures.

Listing 18.1. Writing to a file with PUT, PUTF, and PUT_LINE.

 1: DECLARE
 2: -- Create a file handle of type UTL_FILE.FILE_TYPE
 3:      v_MyFileHandle UTL_FILE.FILE_TYPE;
 4: -- User defined variables to output
 5:      v_FirstName VARCHAR2(15) := `Thomas';
 6:      v_LastName VARCHAR2(20) := `Jefferson';
 7:      v_Occupation VARCHAR2(15) := `President';

                                                 continues

Listing 18.1. continued

 8: BEGIN
 9: -- Open the file to write.
10:      v_MyFileHandle := UTL_FILE.FOPEN(`c:\','myout.txt','w');
11: -- Example of Using PUT
12:      UTL_FILE.PUT(v_MyFileHandle,'First Name: ` || v_FirstName
13:           || ` Last Name: ` || v_LastName
14:           || ` Occupation: ` || v_Occupation);
15:      UTL_FILE.NEW_LINE(v_MyFileHandle,1);
16: -- Repeat but with PUT_LINE
17:      UTL_FILE.PUT_LINE(v_MyFileHandle,'First Name: ` || v_FirstName
18:           || ` Last Name: ` || v_LastName
19:           || ` Occupation: ` || v_Occupation);
20: -- Repeat with PUTF
21:      UTL_FILE.PUTF(v_MyFileHandle,'%s' || v_FirstName ||
22:                ` %s' || v_LastName || ` %s' || v_Occupation || `\n',
23:           `First Name: `,
24:           `Last Name: `,
25:           `Occupation: `);
26: -- Close the file handle which points to myout.txt
27:      UTL_FILE.FCLOSE(v_MyFileHandle);
28: EXCEPTION
29: -- Create Exception to simply display error code and message
30:      WHEN OTHERS THEN
31:           DBMS_OUTPUT.PUT_LINE
32:                 (`ERROR ` || to_char(SQLCODE) || SQLERRM);
33:           NULL;
34: END;

Your output should appear similar to the following:

OUTPUT

First Name: Thomas Last Name: Jefferson Occupation: President
First Name: Thomas Last Name: Jefferson Occupation: President
First Name: Thomas Last Name: Jefferson Occupation: President

The first PUT statement in line 12 is followed by NEW_LINE in line 15, which is identical to PUT_LINE in line 17, which adds the newline character at the end of the line. Finally, PUTF in line 21 demonstrates using strings (up to five per call) and using `\n' in line 22 to output the newline character.

Using FFLUSH to Clear the Buffer
When you use any of the PUT commands, the data is stored in the UTL_FILE package's buffer until it is full, and then the buffer writes to the file. If you need to flush the contents of the buffer and write to disk immediately, Oracle provides the FFLUSH procedure.

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

PROCEDURE FFLUSH(file_handle IN UTL_FILE.FILE_TYPE);

The file_handle parameter is the file handle returned from FOPEN. Refer to Table 18.3 for a list of possible exceptions raised by FFLUSH.

Closing Files

When you have completed reading or writing from a file, you should close the file in order to flush the buffer and then free up resources in Oracle (that is, file handles, and so on). There are two methods to close files: FCLOSE and FCLOSE_ALL.

Using FCLOSE to Close Files
The FCLOSE procedure flushes the buffer (if not empty) and then closes one file specified by the file handle of UTL_FILE.FILE_TYPE.

The Syntax for the FLCLOSE Function
The syntax for FCLOSE is

PROCEDURE FCLOSE(file_handle IN UTL_FILE.FILE_TYPE);

The file_handle parameter is the file handle returned from FOPEN. Refer to Table 18.4 for a list of possible exceptions raised by FCLOSE.

Table 18.4. Exceptions raised by FCLOSE and FCLOSE_ALL.

Using FCLOSE_ALL to Close All Files
The FCLOSE_ALL procedure flushes the buffer (if not empty) and then closes all open files. The format of the FCLOSE_ALL procedure is as follows:

PROCEDURE FCLOSEALL;

Refer to Table 18.4 for a list of possible exceptions raised by FCLOSEALL. You should use FCLOSE_ALL only in emergencies, such as an exception handler to properly close all files for any error that can occur. Improperly closed files can lead to corruption. Always use FCLOSE instead of FCLOSE_ALL for normal file handling.