[Appendix C] C.17 UTL

C.17 UTL_FILE

The UTL_FILE package allows your PL/SQL programs to both read from and write to operating system files. You can call UTL_FILE from within programs stored in the database server or from within client-side application modules, such as those built with Oracle Forms. You can, therefore, interact with operating system files both on the local workstation (client) and on the server disks.

C.17.1 Setting Up UTL_FILE

Before you can read and write operating system files on the server, you must make changes to the INIT.ORA initialization file of your database instance (this is generally a DBA task). Specifically, you must add one or more entries for the utl_file_dir parameter. Each line must have this format:

utl_file_dir = <directory>

where <directory> is either a specific directory or a single asterisk. If your entry has this format:

utl_file_dir = *

then you will be able to read from and write to any directory accessible from your server machine. If you want to enable file I/O for a restricted set of directories, provide separate entries in the INIT.ORA file as shown below:

utl_file_dir = /tmp/trace
utl_file_dir = /user/dev/george/files

The Oracle user must then have operating system privileges on a directory in order to write to it or read from it. Finally, any files created through UTL_FILE will have the default privileges taken from the Oracle user.

C.17.1.1 The FCLOSE procedure

Use FCLOSE to close an open file. The specification is:

PROCEDURE UTL_FILE.FCLOSE (file_in UTL_FILE.FILE_TYPE);

C.17.1.2 The FCLOSE_ALL procedure

FCLOSE_ALL closes all of the opened files. The specification is:

PROCEDURE UTL_FILE.FCLOSE_ALL;

C.17.1.3 The FFLUSH procedure

The FFLUSH procedure flushes the contents of the UTL_FILE buffer out to the specified file. You will want to use FFLUSH to make sure that any buffered messages are written to the file and therefore available for reading. The specification is:

PROCEDURE UTL_FILE.FFLUSH (file IN FILE_TYPE);

C.17.1.4 The FOPEN function

The FOPEN function opens the specified file and returns a file handle that you can then use to manipulate the file. The specification is:

FUNCTION UTL_FILE.FOPEN
   (location_in IN VARCHAR2,
    file_name_in IN VARCHAR2,
    file_mode_in IN VARCHAR2)
RETURN UTL_FILE.FILE_TYPE;

C.17.1.5 The GET_LINE procedure

The GET_LINE procedure reads a line of data from the specified file, if it is open, into the provided line buffer. The specification is:

PROCEDURE UTL_FILE.GET_LINE
   (file_in IN UTL_FILE.FILE_TYPE,
    line_out OUT VARCHAR2);

C.17.1.6 The IS_OPEN function

The IS_OPEN function returns TRUE if the specified handle points to a file that is already open. Otherwise it returns false. The specification is:

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

C.17.1.7 The NEW_LINE procedure

The NEW_LINE procedure inserts one or more newline characters in the specified file. The specification is:

PROCEDURE UTL_FILE.NEW_LINE
   (file_in IN UTL_FILE.FILE_TYPE,
    num_lines_in IN PLS_INTEGER := 1);

C.17.1.8 The PUT procedure

The PUT procedure puts data out to the specified file. The PUT procedure is heavily overloaded so that you can easily call PUT with a number of different combinations of arguments. The specifications are:

PROCEDURE UTL_FILE.PUT
   (file_in UTL_FILE.FILE_TYPE,
    item_in IN VARCHAR2);

PROCEDURE UTL_FILE.PUT (item_in IN VARCHAR2);

PROCEDURE UTL_FILE.PUT
   (file_in UTL_FILE.FILE_TYPE,
    item_in IN DATE);

PROCEDURE UTL_FILE.PUT (item_in IN DATE);

PROCEDURE UTL_FILE.PUT
   (file_in UTL_FILE.FILE_TYPE,
    item_in IN NUMBER);

PROCEDURE UTL_FILE.PUT (item_in IN NUMBER);

PROCEDURE UTL_FILE.PUT
   (file_in UTL_FILE.FILE_TYPE,
    item_in IN PLS_INTEGER);

PROCEDURE UTL_FILE.PUT (item_in IN PLS_INTEGER);

C.17.1.9 The PUTF procedure

Like PUT, PUTF puts data into a file, but it uses a message format (hence, the "F" in "PUTF") to interpret the different elements to be placed in the file. You can pass between one and five different items of data to PUTF. The specification is:

PROCEDURE UTL_FILE.PUTF
   (file_in UTL_FILE.FILE_TYPE,
    format_in IN VARCHAR2,
    item1_in IN VARCHAR2
    [, item2_in IN VARCHAR2 ... item5_in IN VARCHAR2]);

C.17.1.10 The PUT_LINE procedure

The third variation of the PUT feature in UTL_FILE is PUT_LINE. This procedure writes data to a file and then immediately appends a newline character after the text. The specification is:

PROCEDURE UTL_FILE.PUT_LINE
(file_in UTL_FILE.FILE_TYPE,


item_in IN VARCHAR2);


C.16 DBMS_UTILITY