With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

This chapter contains the following topics:

ORACLE UTLFILE Package; ORACLE UTLFILE Exceptions; UTLFILE Operations and Functions; Comma Separated Values; RETURNING Table From a Function; REGULAR Expressions in ORACLE; RESTRICT DROP/TRUNCATE on TABLE; Export Table Data to CSV; IMPORT Data from Flat Files to ORACLE Tables; ORACLE PIVOT/UNPIVOT; PARTITIONING IN ORACLE; Equality Test of.

• • Security Model

  • Types

  • Rules and Limits

  • Exceptions

  • Examples

Using UTL_FILE

Security Model

UTL_FILE is available for both client-side and server-side PL/SQL. Both the client (text I/O) and server implementations are subject to server-side file system permission checking.

In the past, accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is not recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.

Note:

Use the CREATEDIRECTORY feature instead of UTL_FILE_DIR for directory access verification.

On UNIX systems, the owner of a file created by the FOPEN function is the owner of the shadow process running the instance. Normally, 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 may need access from a system administrator.

Caution:

• The privileges needed to access files in a directory object are operating system specific. UTL_FILE directory object privileges give you read and write access to all files within the specified directory.

• Attempting to apply invalid options will give rise to unpredictable results.

Types

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

Operational Notes

The file location and file name parameters are supplied to the FOPEN function as separate strings, so that the file location can be checked against the list of accessible directories as specified by the ALL_DIRECTORIES view of accessible directory objects. Together, the file location and name must represent a legal filename on the system, and the directory must be accessible. A subdirectory of an accessible directory is not necessarily also accessible; it too must be specified using a complete path name matching an ALL_DIRECTORIES object.

UTL_FILE implicitly interprets line terminators on read requests, thereby affecting the number of bytes returned on a GET_LINE call. For example, the len parameter of UTL_FILE.GET_LINE specifies the requested number of bytes of character data. The number of bytes actually returned to the user will be the lesser of:

• The GET_LINElen parameter, or

• The number of bytes until the next line terminator character, or

• The max_linesize parameter specified by UTL_FILE.FOPEN

The FOPENmax_linesize parameter must be a number in the range 1 and 32767. If unspecified, Oracle supplies a default value of 1024. The GET_LINElen parameter must be a number in the range 1 and 32767. If unspecified, Oracle supplies the default value of max_linesize. If max_linesize and len are defined to be different values, then the lesser value takes precedence.

UTL_FILE.GET_RAW ignores line terminators and returns the actual number of bytes requested by the GET_RAWlen parameter.

When data encoded in one character set is read and Globalization Support is told (such as by means of NLS_LANG) that it is encoded in another character set, the result is indeterminate. If NLS_LANG is set, it should be the same as the database character set.

Rules and Limits

Operating system-specific parameters, such as C-shell environment variables under UNIX, cannot be used in the file location or file name parameters.

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

Note:

The UTL_FILE package is similar to the client-side TEXT_IO package currently provided by Oracle Procedure Builder. Restrictions for a server implementation require some API differences between UTL_FILE and TEXT_IO. In PL/SQL file I/O, errors are returned using PL/SQL exceptions.

Exceptions

Table 168-1 UTL_FILE Package Exceptions

Exception Name	Description
INVALID_PATH	File location is invalid.
INVALID_MODE	The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE	File handle is invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Operating system error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error
CHARSETMISMATCH	A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN	The requested operation failed because the file is open.
INVALID_MAXLINESIZE	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME	The filename parameter is invalid.
ACCESS_DENIED	Permission to access to the file location is denied.
INVALID_OFFSET	Causes of the INVALID_OFFSET exception: ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or ABSOLUTE_OFFSET < 0, or Either offset caused a seek past the end of the file
DELETE_FAILED	The requested file delete operation failed.
RENAME_FAILED	The requested file rename operation failed.

Procedures in UTL_FILE can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERROR.

Examples

Example 1

Given the following:

The following file locations and filenames are valid and accessible as follows:

File Location	Filename	Accessible By
/appl/gl/log	L12345.log	Users with DBA privilege
/appl/gl/user	u12345.tmp	All users

The following file locations and filenames are invalid:

File Location	Filename	Invalid Because
/appl/gl/log/backup	L12345.log	# subdirectories are not accessible
/APPL/gl/log	L12345.log	# directory strings must follow case sensitivity rules as required by the O/S
/appl/gl/log	backup/L1234.log	# filenames may not include portions of directory paths
/user/tmp	L12345.log	# no corresponding CREATE DIRECTORY command has been issued

Example 2

Summary of UTL_FILE Subprograms

Table 168-2 UTL_FILE Subprograms

Subprogram	Description
Closes a file
Closes all open file handles
Copies a contiguous portion of a file to a newly created file
Physically writes all pending output to a file
Reads and returns the attributes of a disk file
Returns the current relative offset position within a file, in bytes
Opens a file for input or output
Opens a file in Unicode for input or output
Deletes a disk file, assuming that you have sufficient privileges
Renames an existing file to a new name, similar to the UNIX mv function
Adjusts the file pointer forward or backward within the file by the number of bytes specified
Reads text from an open file
Reads text in Unicode from an open file
Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
Determines if a file handle refers to an open file
Writes one or more operating system-specific line terminators to a file
Writes a string to a file
Writes a line to a file, and so appends an operating system-specific line terminator
Writes a Unicode line to a file
Writes a Unicode string to a file
A PUT procedure with formatting
A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting
Accepts as input a RAW data value and writes the value to the output buffer

FCLOSE Procedure

This procedure closes an open file identified by a file handle.

Syntax

Parameters

Table 168-3 FCLOSE Procedure Parameters

Parameter	Description
file	Active file handle returned by an FOPEN or FOPEN_NCHAR call.

Usage Notes

If there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.

Exceptions

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

Usage Notes

Note:

FCLOSE_ALL does not alter the state of the open file handles held by the user. This means that an IS_OPEN test on a file handle after an FCLOSE_ALL call still returns TRUE, even though the file has been closed. No further read or write operations can be performed on a file that was open before an FCLOSE_ALL.

Exceptions

FCOPY Procedure

This procedure copies a contiguous portion of a file to a newly created file. By default, the whole file is copied if the start_line and end_line parameters are omitted. The source file is opened in read mode. The destination file is opened in write mode. A starting and ending line number can optionally be specified to select a portion from the center of the source file for copying.

Syntax

Parameters

Table 168-4 FCOPY Procedure Parameters

Parameters	Description
location	The directory location of the source file, a DIRECTORY_NAME from the ALL_DIRECTORIES view (case sensitive)
filename	The source file to be copied
dest_dir	The destination directory where the destination file is created.
dest_file	The destination file created from the source file.
start_line	The line number at which to begin copying. The default is 1 for the first line.
end_line	The line number at which to stop copying. The default is NULL, signifying end of file.

FFLUSH Procedure

FFLUSH physically writes pending data to the file identified by the file handle. Normally, data being written to a file is buffered. The FFLUSH procedure forces the buffered data to be written to the file. The data must be terminated with a newline character.

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

Parameters

Table 168-5 FFLUSH Procedure Parameters

Parameters	Description
file	Active file handle returned by an FOPEN or FOPEN_NCHAR call.

Exceptions

FGETATTR Procedure

This procedure reads and returns the attributes of a disk file.

Syntax

Parameters

Table 168-6 FGETATTR Procedure Parameters

Parameters	Description
location	Directory location of the source file, a DIRECTORY_NAME from the ALL_DIRECTORIES view (case sensitive).
filename	The name of the file to be examined.
exists	A BOOLEAN for whether or not the file exists.
file_length	The length of the file in bytes. NULL if file does not exist.
blocksize	The file system block size in bytes. NULL if the file does not exist.

FGETPOS Function

This function returns the current relative offset position within a file, in bytes.

Syntax

Parameters

Table 168-7 FGETPOS Parameters

Parameters	Description
fileid	The directory location of the source file

Return Values

FGETPOS returns the relative offset position for an open file, in bytes. It raises an exception if the file is not open. It returns 0 for the beginning of the file.

FOPEN Function

This function opens a file. You can specify the maximum line size and have a maximum of 50 files open simultaneously. See also FOPEN_NCHAR Function.

Syntax

Parameters

Table 168-8 FOPEN Function Parameters

Parameter	Description
location	Directory location of file. This string is a directory object name and is case sensitive. The default is uppercase. Read privileges must be granted on this directory object for the UTL_FILE user to run FOPEN.
filename	File name, including extension (file type), without directory path. If a directory path is given as a part of the filename, it is ignored by FOPEN. On Unix, the filename cannot end with /.
open_mode	Specifies how the file is opened. Modes include: r -- read text w -- write text a -- append text rb -- read byte mode wb -- write byte mode ab -- append byte mode If you try to open a file specifying 'a' or 'ab' for open_mode but the file does not exist, the file is created in write mode.
max_linesize	Maximum number of characters for each line, including the newline character, for this file (minimum value 1, maximum value 32767). If unspecified, Oracle supplies a default value of 1024.

Return Values

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.

Table 168-9 FOPEN Function Return Values

Return	Description
file_type	Handle to open file.

Usage Notes

The file location and file name parameters must be supplied to the FOPEN function as quoted strings so that the file location can be checked against the list of accessible directories as specified by the ALL_DIRECTORIES view of accessible directory objects.

Exceptions

FOPEN_NCHAR Function

This function opens a file in Unicode for input or output, with the maximum line size specified. You can have a maximum of 50 files open simultaneously. With this function, you can read or write a text file in Unicode instead of in the database charset. See also FOPEN Function.

Syntax

Parameters

Table 168-10 FOPEN_NCHAR Function Parameters

Parameter	Description
location	Directory location of file.
filename	File name (including extension).
open_mode	Open mode (r,w,a,rb,wb,ab).
max_linesize	Maximum number of characters for each line, including the newline character, for this file. (minimum value 1, maximum value 32767).

FREMOVE Procedure

This procedure deletes a disk file, assuming that you have sufficient privileges.

Syntax

Parameters

Table 168-11 FREMOVE Procedure Parameters

Parameters	Description
location	The directory location of the file, a DIRECTORY_NAME from ALL_DIRECTORIES (case sensitive)
filename	The name of the file to be deleted

Usage Notes

The FREMOVE procedure does not verify privileges before deleting a file. The O/S verifies file and directory permissions. An exception is returned on failure.

FRENAME Procedure

This procedure renames an existing file to a new name, similar to the UNIX mv function.

Syntax

Parameters

Table 168-12 FRENAME Procedure Parameters

Parameters	Description
location	The directory location of the source file, a DIRECTORY_NAME from the ALL_DIRECTORIES view (case sensitive).
filename	The source file to be renamed.
dest_dir	The destination directory of the destination file, a DIRECTORY_NAME from the ALL_DIRECTORIES view (case sensitive).
dest_file	The new name of the file.
overwrite	The default is FALSE.

Usage Notes

Permission on both the source and destination directories must be granted. You can use the overwrite parameter to specify whether or not to overwrite a file if one exists in the destination directory. The default is FALSE for no overwrite.

FSEEK Procedure

This procedure adjusts the file pointer forward or backward within the file by the number of bytes specified.

Syntax

Parameters

Table 168-13 FSEEK Procedure Parameters

Parameters	Description
fid	The file ID.
absolute_offset	The absolute location to which to seek; default = NULL
relative_offset	The number of bytes to seek forward or backward; positive = forward, negative integer = backward, zero = current position, default = NULL

Usage Notes

Using FSEEK, you can read previous lines in the file without first closing and reopening the file. You must know the number of bytes by which you want to navigate.

If relative_offset, the procedure seeks forward. If relative_offset > 0, or backward, if relative_offset < 0, the procedure seeks through the file by the number of relative_offset bytes specified.

If the beginning of the file is reached before the number of bytes specified, then the file pointer is placed at the beginning of the file. If the end of the file is reached before the number of bytes specified, then an INVALID_OFFSET error is raised.

If absolute_offset, the procedure seeks to an absolute location specified in bytes.

GET_LINE Procedure

This procedure reads 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, or up to the end of the len parameter. It cannot exceed the max_linesize specified in FOPEN.

Syntax

Parameters

Table 168-14 GET_LINE Procedure Parameters

Parameters	Description
file	Active file handle returned by an FOPEN call. The file must be open for reading (mode r); otherwise an INVALID_OPERATION exception is raised.
buffer	Data buffer to receive the line read from the file.
len	The number of bytes read from the file. Default is NULL. If NULL, Oracle supplies the value of max_linesize.

Usage Notes

If the line does not fit in the buffer, a VALUE_ERROR exception is raised. If no text was read due to end of file, the NO_DATA_FOUND exception is raised. If the file is opened for byte mode operations, the INVALID_OPERATION exception is raised.

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

The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN.If unspecified, Oracle supplies a default value of 1024. See also 'GET_LINE_NCHAR Procedure'.

Exceptions

GET_LINE_NCHAR Procedure

This procedure reads text from the open file identified by the file handle and places the text in the output buffer parameter. With this function, you can read a text file in Unicode instead of in the database charset. See also GET_LINE Procedure.

Syntax

Parameters

Table 168-15 GET_LINE_NCHAR Procedure Parameters

Parameters	Description
file	Active file handle returned by an FOPEN_NCHAR call. The file must be open for reading (mode r). If the file is opened by FOPEN instead of FOPEN_NCHAR, a CHARSETMISMATCH exception is raised.
buffer	Data buffer to receive the line read from the file.
len	The number of bytes read from the file. Default is NULL. If NULL, Oracle supplies the value of max_linesize.

GET_RAW Function

This function reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read. UTL_FILE.GET_RAW ignores line terminators and returns the actual number of bytes requested by the GET_RAWlen parameter.

Syntax

Parameters

Table 168-16 GET_RAW Procedure Parameters

Parameters	Description
fid	The file ID.
r	The RAW data.
len	The number of bytes read from the file. Default is NULL. If NULL, len is assumed to be the maximum length of RAW.

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

Parameters

Table 168-17 IS_OPEN Function Parameters

Parameter	Description
file	Active file handle returned by an FOPEN or FOPEN_NCHAR call.

Return Values

TRUE or FALSE

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

Parameters

Table 168-18 NEW_LINE Procedure Parameters

Parameters	Description
file	Active file handle returned by an FOPEN or FOPEN_NCHAR call.
lines	Number of line terminators to be written to the file.

Exceptions

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. See also 'PUT_NCHAR Procedure'.

Syntax

Parameters

Table 168-19 PUT Procedure Parameters

Parameters	Description
file	Active file handle returned by an FOPEN_NCHAR call. The file must be open for writing.
buffer	Buffer that contains the text to be written to the file. You must have opened the file using mode w or mode a; otherwise, an INVALID_OPERATION exception is raised.

Usage Notes

The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.

Exceptions

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 Windows drivers don’t have useful tools like NVIDIA GeForce Experience and AMD ReLive that you get from the manufacturer, either. Device connected but driver is too old.

See also 'PUT_LINE_NCHAR Procedure'.

Syntax

Parameters

Table 168-20 PUT_LINE Procedure Parameters

Parameters	Description
file	Active file handle returned by an FOPEN call.
buffer	Text buffer that contains the lines to be written to the file.
autoflush	Flushes the buffer to disk after the WRITE.

Usage Notes

The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.

Exceptions

PUT_LINE_NCHAR Procedure

This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. With this function, you can write a text file in Unicode instead of in the database charset. See also 'PUT_LINE Procedure'.

Syntax

Parameters

Table 168-21 PUT_LINE_NCHAR Procedure Parameters

Parameters	Description
file	Active file handle returned by an FOPEN_NCHAR call. The file must be open for writing.
buffer	Text buffer that contains the lines to be written to the file.

Usage Notes

The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.

PUT_NCHAR Procedure

This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. With this function, you can write a text file in Unicode instead of in the database charset. See also 'PUT Procedure'.

Syntax

Parameters

Table 168-22 PUT_NCHAR Procedure Parameters

Parameters	Description
file	Active file handle returned by an FOPEN_NCHAR call. If the file is opened by FOPEN instead of FOPEN_NCHAR, a CHARSETMISMATCH exception is raised.
buffer	Buffer that contains the text to be written to the file. You must have opened the file using mode w or mode a; otherwise, an INVALID_OPERATION exception is raised.

Usage Notes

The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.

PUTF Procedure

This procedure is a formatted PUT procedure. It works like a limited printf(). See also 'PUTF_NCHAR Procedure'.

Syntax

Parameters

Table 168-23 PUTF Procedure Parameters

Parameters	Description
file	Active file handle returned by an FOPEN call.
format	Format string that can contain text as well as the formatting characters n and %s.
arg1.arg5	From one to five operational argument strings. Argument strings are substituted, in order, for the %s formatters in the format string. If there are more formatters in the format parameter string than there are arguments, then an empty string is substituted for each %s for which there is no argument.

Usage Notes

The format string can contain any text, but the character sequences %s and n have special meaning.

Character Sequence	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.

Examples

The following example writes the lines:

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

PUTF_NCHAR Procedure

This procedure is a formatted PUT_NCHAR procedure. Using PUTF_NCHAR, you can write a text file in Unicode instead of in the database charset.

Syntax

Parameters

Table 168-24 PUTF_NCHAR Procedure Parameters

Parameters	Description
file	Active file handle returned by an FOPEN_NCHAR call. The file must be open for reading (mode r). If the file is opened by FOPEN instead of FOPEN_NCHAR, a CHARSETMISMATCH exception is raised.
format	Format string that can contain text as well as the formatting characters n and %s.
arg1.arg5	From one to five operational argument strings. Argument strings are substituted, in order, for the %s formatters in the format string. If there are more formatters in the format parameter string than there are arguments, then an empty string is substituted for each %s for which there is no argument.

Usage Notes

The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.

PUT_RAW Function

This function accepts as input a RAW data value and writes the value to the output buffer.

Syntax

Parameters

Table 168-25 PUT_RAW Procedure Parameters

Parameters	Description
fid	The file ID.
r	The RAW data written to the buffer.
autoflush	If TRUE, performs a flush after writing the value to the output buffer; default is FALSE.

Usage Notes

You can request an automatic flush of the buffer by setting the third argument to TRUE.

The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.