57
UTL_FILE

The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).

The file I/O capabilities are similar to those of the standard operating system stream file I/O (OPEN, GET, PUT, CLOSE), with some limitations. For example, call the FOPEN function to return a file handle, which you then use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When you are done performing I/O on the file, call FCLOSE to complete any output and to free any resources associated with the file.

Security

The PL/SQL file I/O feature is available for both client side and server side PL/SQL. The client implementation (text I/O) is subject to normal operating system file permission checking, and it does not need any additional security constraints. However, the server implementation might be running in a privileged mode, and will need additional security restrictions that limit the power of this feature.

Server Security

Server security for PL/SQL file I/O consists of a restriction on the directories that can be accessed. Accessible directories must be specified in the instance parameter initialization file (INIT.ORA).

Specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter. For example:

UTL_FILE_DIR = <directory name>

If the initialization file for the instance contains the line UTL_FILE_DIR = /usr/jsmith/my_app, then the directory /usr/jsmith/my_app is accessible to the FOPEN function. Note that a directory named /usr/jsmith/My_App would not be accessible on case-sensitive operating systems.

The parameter specification UTL_FILE_DIR = * has a special meaning. This entry turns off directory access checking, and it makes any directory accessible to the UTL_FILE functions.

File Ownership and Protections

On UNIX systems, a file created by the FOPEN function has as its owner the owner of the shadow process running the instance. In the normal case, this owner is oracle. Files created using FOPEN are always writable and readable using the UTL_FILE subprograms, but non-privileged users who need to read these files outside of PL/SQL might nned their system administrator to give them access.

Examples (UNIX-Specific)

If the parameter initialization file contains only:

UTL_FILE_DIR=/appl/gl/log
UTL_FILE_DIR=/appl/gl/out

Then, the following file locations and filenames are valid:

FILE LOCATION             FILENAME
/appl/gl/log              L10324.log
/appl/gl/out              O10324.out

But, the following file locations and filename are invalid:

FILE LOCATION             FILENAME
/appl/gl/log/backup       L10324.log          # subdirectory
/APPL/gl/log              L10324.log          # uppercase
/appl/gl/log              backup/L10324.log   # dir in name
/usr/tmp                  T10324.tmp          # not in INIT.ORA

Types

TYPE file_type IS RECORD (id BINARY_INTEGER);

The contents of FILE_TYPE are private to the UTL_FILE package. Users of the package should not reference or change components of this record.

Exceptions

Table 57-1 UTL_FILE Package Exceptions

INVALID_PATH

INVALID_MODE

INVALID_FILEHANDLE

INVALID_OPERATION

READ_ERROR

WRITE_ERROR

INTERNAL_ERROR

In addition to these package exceptions, procedures in UTL_FILE can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERROR.

Summary of Subprograms

Table 57-2 UTL_FILE Subprograms

FOPEN function

IS_OPEN function

FCLOSE procedure

FCLOSE_ALL procedure

GET_LINE procedure

PUT procedure

NEW_LINE procedure

PUT_LINE procedure

PUTF procedure

FFLUSH procedure

FOPEN function

FOPEN function

This function opens a file for input or output. The file location must be an accessible directory, as defined in the instance's initialization parameter UTL_FILE_DIR. The complete directory path must already exist; it is not created by FOPEN.

FOPEN returns a file handle, which must be used in all subsequent I/O operations on the file.

This version of FOPEN does not take a parameter for the maximum line size. Thus, the default (which is 1023 on most systems) is used. To specify a different maximum line size, use the other, overloaded version of "FOPEN function".

You can have a maximum of 50 files open simultaneously.

Syntax

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

Parameters

Table 57-3 FOPEN Function Parameters

location

filename

open_mode

Returns

FOPEN returns a file handle, which must be passed to all subsequent procedures that operate on that file. The specific contents of the file handle are private to the UTL_FILE package, and individual components should not be referenced or changed by the UTL_FILE user.

Exceptions

INVALID_PATH
INVALID_MODE
INVALID_OPERATION

IS_OPEN function

This function tests a file handle to see if it identifies an open file. IS_OPEN reports only whether a file handle represents a file that has been opened, but not yet closed. It does not guarantee that there will be no operating system errors when you attempt to use the file handle.

Syntax

UTL_FILE.IS_OPEN (
   file  IN FILE_TYPE)
  RETURN BOOLEAN;

Parameters

Table 57-4 IS_OPEN Function Parameters

file

Returns

TRUE or FALSE

Exceptions

None.

FCLOSE procedure

This procedure closes an open file identified by a file handle. If there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.

Syntax

UTL_FILE.FCLOSE (
   file IN OUT FILE_TYPE);

Parameters

Table 57-5 FCLOSE Procedure Parameters

file

Exceptions

WRITE_ERROR
INVALID_FILEHANDLE

FCLOSE_ALL procedure

This procedure closes all open file handles for the session. This should be used as an emergency cleanup procedure, for example, when a PL/SQL program exits on an exception.

Syntax

UTL_FILE.FCLOSE_ALL;

Parameters

None.

Exceptions

WRITE_ERROR

GET_LINE procedure

This procedure reads a line of text from the open file identified by the file handle and places the text in the output buffer parameter. Text is read up to but not including the line terminator, or up to the end of the file.

If the line does not fit in the buffer, then a VALUE_ERROR exception is raised. If no text was read due to "end of file," then the NO_DATA_FOUND exception is raised.

Because the line terminator character is not read into the buffer, reading blank lines returns empty strings.

The maximum size of an input record is 1023 bytes, unless you specify a larger size in the overloaded version of FOPEN.

Syntax

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

Parameters

Table 57-6 GET_LINE Procedure Parameters

file

buffer

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
NO_DATA_FOUND
VALUE_ERROR

PUT procedure

PUT writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations. No line terminator is appended by PUT; use NEW_LINE to terminate the line or use PUT_LINE to write a complete line with a line terminator.

The maximum size of an input record is 1023 bytes, unless you specify a larger size in the overloaded version of FOPEN.

Syntax

UTL_FILE.PUT (
   file      IN FILE_TYPE,
   buffer    IN VARCHAR2);

Parameters

Table 57-7 PUT Procedure Parameters

file

buffer

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

NEW_LINE procedure

This procedure writes one or more line terminators to the file identified by the input file handle. This procedure is separate from PUT because the line terminator is a platform-specific character or sequence of characters.

Syntax

UTL_FILE.NEW_LINE (
   file     IN FILE_TYPE,
   lines    IN NATURAL := 1);

Parameters

Table 57-8 NEW_LINE Procedure Parameters

file

lines

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

PUT_LINE procedure

This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations. PUT_LINE terminates the line with the platform-specific line terminator character or characters.

The maximum size for an output record is 1023 bytes, unless you specify a larger value using the overloaded version of FOPEN.

Syntax

UTL_FILE.PUT_LINE (
   file    IN FILE_TYPE,
   buffer  IN VARCHAR2);

Parameters

Table 57-9 PUT_LINE Procedure Parameters

file

buffer

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

PUTF procedure

This procedure is a formatted PUT procedure. It works like a limited printf(). The format string can contain any text, but the character sequences '%s' and '\n' have special meaning.

%s	Substitute this sequence with the string value of the next argument in the argument list.
\n	Substitute with the appropriate platform-specific line terminator.

Syntax

UTL_FILE.PUTF (
   file    IN FILE_TYPE,
   format  IN VARCHAR2,
   [arg1   IN VARCHAR2  DEFAULT NULL,
   . . .  
   arg5    IN VARCHAR2  DEFAULT NULL]); 

Parameters

Table 57-10 PUTF Procedure Parameters

file

format

arg1..arg5

Example

The following example writes the lines:

Hello, world!
I come from Zork with greetings for all earthlings.

my_world  varchar2(4) := 'Zork';
...
PUTF(my_handle, 'Hello, world!\nI come from %s with %s.\n',
                my_world,
                'greetings for all earthlings');

If there are more %s formatters in the format parameter than there are arguments, then an empty string is substituted for each %s for which there is no matching argument.

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

FFLUSH procedure

FFLUSH physically writes all pending data to the file identified by the file handle. Normally, data being written to a file is buffered. The FFLUSH procedure forces any buffered data to be written to the file.

Flushing is useful when the file must be read while still open. For example, debugging messages can be flushed to the file so that they can be read immediately.

Syntax

UTL_FILE.FFLUSH (
   file  IN FILE_TYPE);
invalid_maxlinesize  EXCEPTION;

Parameters

Table 57-11 FFLUSH Procedure Parameters

file

Exceptions

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

FOPEN function

This function opens a file. You can have a maximum of 50 files open simultaneously.

Syntax

UTL_FILE.FOPEN (
   location     IN VARCHAR2,
   filename     IN VARCHAR2,
   open_mode    IN VARCHAR2,
   max_linesize IN BINARY_INTEGER) 
  RETURN file_type;

Parameters

Table 57-12 FOPEN Function Parameters

location

filename

open_mode

max_linesize

Returns

Table 57-13 FOPEN Function Returns

file_type

Exceptions

INVALID_PATH: File location or name was invalid.
INVALID_MODE: The open_mode string was invalid.
INVALID_OPERATION: File could not be opened as requested.
INVALID_MAXLINESIZE: Specified max_linesize is too large or too small.