285 UTL_FILE
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:
285.1 UTL_FILE Security Model
The set of files and directories that are accessible to the user through UTL_FILE
is controlled by a number of factors and database parameters. Foremost of these is the set of directory objects that have been granted to the user.
The nature of directory objects is discussed in the Oracle Database SQL Language Reference.
Assuming the user has both READ
and WRITE
access to the directory object USER_DIR
, the user can open a file located in the operating system directory described by USER_DIR
, but not in subdirectories or parent directories of this directory.
Lastly, the client (text I/O) and server implementations are subject to operating system file permission checking.
UTL_FILE
provides file access both on the client side and on the server side. When run on the server, UTL_FILE
provides access to all operating system files that are accessible from the server. On the client side, as in the case for Forms applications, UTL_FILE
provides access to operating system files that are accessible from the client.
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
ANY
DIRECTORY
privilege is granted only to SYS
and SYSTEM
by default.
Note:
Use the CREATE
DIRECTORY
feature for directory access verification.
Note that neither hard nor symbolic links are supported.
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. However, non-privileged operating system users who need to read these files outside of PL/SQL may need access from a system administrator.
WARNING:
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.
285.2 UTL_FILE Operational Notes
Keep these notes in mind when using UTL_FILE
.
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_LINE
len
parameter, or -
The number of bytes until the next line terminator character, or
-
The
max_linesize
parameter specified byUTL_FILE.FOPEN
The FOPEN
max_linesize
parameter must be a number in the range 1 and 32767. If unspecified, Oracle supplies a default value of 1024. The GET_LINE
len
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.
UTL_FILE
expects that files opened by UTL_FILE.FOPEN
in text mode are encoded in the database character set. It expects that files opened by UTL_FILE.FOPEN_NCHAR
in text mode are encoded in the UTF8 character set. If an opened file is not encoded in the expected character set, the result of an attempt to read the file is indeterminate. 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 therefore be the same as the database character set.
285.3 UTL_FILE 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.
285.4 UTL_FILE Exceptions
This table describes exceptions raised by UTL_FILE
subprograms.
Table 285-1 UTL_FILE Package Exceptions
Exception Name | Description |
---|---|
|
File location is invalid. |
|
The |
|
File handle is invalid. |
|
File could not be opened or operated on as requested. |
|
Destination buffer too small, or operating system error occurred during the read operation |
|
Operating system error occurred during the write operation. |
|
Unspecified PL/SQL error |
|
A file is opened using |
|
The requested operation failed because the file is open. |
|
The |
|
The filename parameter is invalid. |
|
Permission to access to the file location is denied. |
|
Causes of the
|
|
The requested file delete operation 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
.
285.5 UTL_FILE Examples
These two examples show use of the procedure.
Example 1
Note:
The examples are UNIX-specific.
Given the following:
SQL> CREATE DIRECTORY log_dir AS '/appl/gl/log'; SQL> GRANT READ ON DIRECTORY log_dir TO DBA; SQL> GRANT WRITE ON DIRECTORY log_dir TO DBA; SQL> CREATE DIRECTORY USER_DIR AS '/appl/gl/user''; SQL> GRANT READ ON DIRECTORY USER_DIR TO PUBLIC; SQL> GRANT WRITE ON DIRECTORY USER_DIR TO PUBLIC;
The following file locations and filenames are valid and accessible as follows:
File Location | Filename | READ and WRITE |
---|---|---|
|
|
Users with DBA privilege |
|
|
All users |
The following file locations and filenames are invalid:
File Location | Filename | Invalid Because |
---|---|---|
|
|
# subdirectories are not accessible |
|
|
# directory strings must follow case sensitivity rules as required by the O/S |
|
|
# filenames may not include portions of directory paths |
|
|
# no corresponding |
Example 2
DECLARE V1 VARCHAR2(32767); F1 UTL_FILE.FILE_TYPE; BEGIN -- In this example MAX_LINESIZE is less than GET_LINE's length request -- so the number of bytes returned will be 256 or less if a line terminator is seen. F1 := UTL_FILE.FOPEN('USER_DIR','u12345.tmp','R',256); UTL_FILE.GET_LINE(F1,V1,32767); UTL_FILE.FCLOSE(F1); -- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024, -- so the number of bytes returned will be 1024 or less if a line terminator is seen. F1 := UTL_FILE.FOPEN('USER_DIR','u12345.tmp','R'); UTL_FILE.GET_LINE(F1,V1,32767); UTL_FILE.FCLOSE(F1); -- In this example, GET_LINE doesn't specify a number of bytes, so it defaults to -- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024. -- So the number of bytes returned will be 1024 or less if a line terminator is seen. F1 := UTL_FILE.FOPEN('USER_DIR','u12345.tmp','R'); UTL_FILE.GET_LINE(F1,V1); UTL_FILE.FCLOSE(F1); END;
285.6 UTL_FILE Data Structures
The UTL_FILE
package defines a RECORD type.
Record Types
285.6.1 FILETYPE Record Type
The contents of FILE_TYPE
are private to the UTL_FILE
package. You should not reference or change components of this record.
TYPE file_type IS RECORD ( id BINARY_INTEGER, datatype BINARY_INTEGER, byte_mode BOOLEAN);
Fields
Table 285-2 FILE_TYPE Fields
Field | Description |
---|---|
|
A numeric value indicating the internal file handle number |
|
Indicates whether the file is a |
|
Indicates whether the file was open as a binary file, or as a text file |
Caution:
Oracle does not guarantee the persistence of FILE_TYPE
values between database sessions or within a single session. Attempts to clone file handles or use dummy file handles may have inderterminate outcomes.
285.7 Summary of UTL_FILE Subprograms
This table lists the UTL_FILE
subprograms and briefly describes them.
Table 285-3 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 |
|
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 |
|
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 |
|
A |
|
Accepts as input a |
285.7.1 FCLOSE Procedure
This procedure closes an open file identified by a file handle.
Syntax
UTL_FILE.FCLOSE ( file IN OUT FILE_TYPE);
Parameters
Table 285-4 FCLOSE Procedure Parameters
Parameter | Description |
---|---|
|
Active file handle returned by an |
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
WRITE_ERROR INVALID_FILEHANDLE
285.7.2 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;
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
WRITE_ERROR
285.7.3 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
UTL_FILE.FCOPY ( src_location IN VARCHAR2, src_filename IN VARCHAR2, dest_location IN VARCHAR2, dest_filename IN VARCHAR2, start_line IN BINARY_INTEGER DEFAULT 1, end_line IN BINARY_INTEGER DEFAULT NULL);
Parameters
Table 285-5 FCOPY Procedure Parameters
Parameters | Description |
---|---|
|
Directory location of the source file, a |
|
Source file to be copied |
|
Destination directory where the destination file is created |
|
Destination file created from the source file |
|
Line number at which to begin copying. The default is |
|
Line number at which to stop copying. The default is |
Exceptions
INVALID_FILENAME
INVALID_PATH
INVALID_OPERATION
INVALID_OFFSET
READ_ERROR
WRITE_ERROR
285.7.4 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
UTL_FILE.FFLUSH ( file IN FILE_TYPE);
Parameters
Table 285-6 FFLUSH Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
Exceptions
INVALID_FILENAME
INVALID_MAXLINESIZE
INVALID_OPERATION
WRITE_ERROR
285.7.5 FGETATTR Procedure
This procedure reads and returns the attributes of a disk file.
Syntax
UTL_FILE.FGETATTR( location IN VARCHAR2, filename IN VARCHAR2, fexists OUT BOOLEAN, file_length OUT NUMBER, block_size OUT BINARY_INTEGER);
Parameters
Table 285-7 FGETATTR Procedure Parameters
Parameters | Description |
---|---|
|
Directory location of the source file, a |
|
Name of the file to be examined |
|
A |
|
Length of the file in bytes. |
|
File system block size in bytes. |
Exceptions
INVALID_PATH
INVALID_FILENAME
INVALID_OPERATION
READ_ERROR
ACCESS_DENIED
285.7.6 FGETPOS Function
This function returns the current relative offset position within a file, in bytes.
Syntax
UTL_FILE.FGETPOS ( file IN FILE_TYPE) RETURN PLS_INTEGER;
Parameters
Table 285-8 FGETPOS Parameters
Parameters | Description |
---|---|
|
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.
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
Usage Notes
If file is opened for byte mode operations, then the INVALID
OPERATION
exception is raised.
285.7.7 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
UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER DEFAULT 1024) RETURN FILE_TYPE;
Parameters
Table 285-9 FOPEN Function Parameters
Parameter | Description |
---|---|
|
Directory location of file. This string is a directory object name and must be specified in upper case. Read privileges must be granted on this directory object for the |
|
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 |
|
Specifies how the file is opened. Modes include:
If you try to open a file specifying ' |
|
Maximum number of bytes 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 285-10 FOPEN Function Return Values
Return | Description |
---|---|
|
Handle to open file |
Exceptions
INVALID_MAXILINESIZE
INVALID_MODE
INVALID_OPERATION
INVALID_PATH
INVALID_FILENAME
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.
285.7.8 FOPEN_NCHAR Function
This function opens a file in national character set mode for input or output, with the maximum line size specified. With this function, you can read or write a text file in Unicode instead of in the database character set.
You can have a maximum of 50 files open simultaneously.
Even though the contents of an NVARCHAR2
buffer may be AL16UTF16 or UTF8 (depending on the national character set of the database), the contents of the file are always read and written in UTF8. UTL_FILE
converts between UTF8 and AL16UTF16 as necessary.
See also FOPEN Function.
Syntax
UTL_FILE.FOPEN_NCHAR ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER DEFAULT 1024) RETURN FILE_TYPE;
Parameters
Table 285-11 FOPEN_NCHAR Function Parameters
Parameter | Description |
---|---|
|
Directory location of file |
|
File name (including extension) |
|
Open mode ( |
|
Maximum number of characters for each line, including the newline character, for this file (minimum value 1, maximum value 32767) |
Return Values
FOPEN_NCHAR
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 285-12 FOPEN_NCHAR Function Return Values
Return | Description |
---|---|
|
Handle to open file |
Exceptions
INVALID_MAXILINESIZE
INVALID_MODE
INVALID_OPERATION
INVALID_PATH
285.7.9 FREMOVE Procedure
This procedure deletes a disk file, assuming that you have sufficient privileges.
Syntax
UTL_FILE.FREMOVE ( location IN VARCHAR2, filename IN VARCHAR2);
Parameters
Table 285-13 FREMOVE Procedure Parameters
Parameters | Description |
---|---|
|
Directory location of the file, a |
|
Name of the file to be deleted |
Exceptions
ACCESS_DENIED
DELETE_FAILED
INVALID_FILENAME
INVALID_OPERATION
INVALID_PATH
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.
285.7.10 FRENAME Procedure
This procedure renames an existing file to a new name, similar to the UNIX mv
function.
Syntax
UTL_FILE.FRENAME ( src_location IN VARCHAR2, src_filename IN VARCHAR2, dest_location IN VARCHAR2, dest_filename IN VARCHAR2, overwrite IN BOOLEAN DEFAULT FALSE);
Parameters
Table 285-14 FRENAME Procedure Parameters
Parameters | Description |
---|---|
|
Directory location of the source file, a |
|
Source file to be renamed |
|
Destination directory of the destination file, a |
|
New name of the file |
|
Default is |
Exceptions
ACCESS_DENIED
INVALID_FILENAME
INVALID_PATH
RENAME_FAILED
285.7.11 FSEEK Procedure
This procedure adjusts the file pointer forward or backward within the file by the number of bytes specified.
Syntax
UTL_FILE.FSEEK ( file IN OUT UTL_FILE.FILE_TYPE, absolute_offset IN PL_INTEGER DEFAULT NULL, relative_offset IN PLS_INTEGER DEFAULT NULL);
Parameters
Table 285-15 FSEEK Procedure Parameters
Parameters | Description |
---|---|
|
File handle |
|
Absolute location to which to seek; default = |
|
Number of bytes to seek forward or backward; positive = forward, negative integer = backward, zero = current position, default = |
Exceptions
INVALID_FILEHANDLE
INVALID_OFFSET
INVALID_OPERATION
READ_ERROR
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. Ifrelative_offset
> 0, or backward, ifrelative_offset
< 0, the procedure seeks through the file by the number ofrelative_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. -
If file is opened for byte mode operations, then the
INVALID
OPERATION
exception is raised.
285.7.12 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
UTL_FILE.GET_LINE ( file IN FILE_TYPE, buffer OUT VARCHAR2, len IN PLS_INTEGER DEFAULT NULL);
Parameters
Table 285-16 GET_LINE Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an The file must be open for reading (mode |
|
Data buffer to receive the line read from the file |
|
The number of bytes read from the file. Default is |
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
NO_DATA_FOUND
READ_ERROR
Usage Notes
If the line does not fit in the buffer, a READ_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".
285.7.13 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 character set.
The file must be opened in national character set mode, and must be encoded in the UTF8 character set. The expected buffer datatype is NVARCHAR2
. If a variable of another datatype, such as NCHAR
, NCLOB
, or VARCHAR2
is specified, PL/SQL will perform standard implicit conversion from NVARCHAR2
after the text is read.
See also GET_LINE Procedure
Syntax
UTL_FILE.GET_LINE_NCHAR ( file IN FILE_TYPE, buffer OUT NVARCHAR2, len IN PLS_INTEGER DEFAULT NULL);
Parameters
Table 285-17 GET_LINE_NCHAR Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Data buffer to receive the line read from the file |
|
The number of bytes read from the file. Default is |
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
NO_DATA_FOUND
READ_ERROR
285.7.14 GET_RAW Procedure
This procedure 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.
Syntax
UTL_FILE.GET_RAW ( file IN UTL_FILE.FILE_TYPE, buffer OUT NOCOPY RAW, len IN PLS_INTEGER DEFAULT NULL);
Parameters
Table 285-18 GET_RAW Procedure Parameters
Parameters | Description |
---|---|
|
File handle |
|
|
|
The number of bytes read from the file. Default is |
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
LENGTH_MISMATCH
NO_DATA_FOUND
READ_ERROR
Usage Notes
The subprogram will raise No_Data_Found
when it attempts to read past the end of the file. Your application should allow for this by catching the exception in its processing loop.
PROCEDURE Sys.p (n IN VARCHAR2) IS h UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN('D', n, 'r', 32767); Buf RAW(32767); Amnt CONSTANT PLS_INTEGER := 32767; BEGIN LOOP BEGIN Utl_File.Get_Raw(h, Buf, Amnt); -- Do something with this chunk EXCEPTION WHEN No_Data_Found THEN EXIT; END; END LOOP; UTL_FILE.FCLOSE (h); END;
285.7.15 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 285-19 IS_OPEN Function Parameters
Parameter | Description |
---|---|
|
Active file handle returned by an |
Return Values
TRUE
or FALSE
Exceptions
INVALID_FILEHANDLE
285.7.16 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 BINARY_INTEGER := 1);
Parameters
Table 285-20 NEW_LINE Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Number of line terminators to be written to the file |
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
285.7.17 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
UTL_FILE.PUT ( file IN FILE_TYPE, buffer IN VARCHAR2);
Parameters
Table 285-21 PUT Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Buffer that contains the text to be written to the file. User must have opened the file using mode |
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
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
285.7.18 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.
See also "PUT_LINE_NCHAR Procedure".
Syntax
UTL_FILE.PUT_LINE ( file IN FILE_TYPE, buffer IN VARCHAR2, autoflush IN BOOLEAN DEFAULT FALSE);
Parameters
Table 285-22 PUT_LINE Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Text buffer that contains the lines to be written to the file |
|
Flushes the buffer to disk after the |
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
Usage Notes
-
The maximum size of the
buffer
parameter is 32767 bytes unless you specify a smaller size inFOPEN
. If unspecified, Oracle supplies a default value of 1024. The sum of all sequentialPUT
calls cannot exceed 32767 without intermediate buffer flushes. -
If file is opened for byte mode operations, then the
INVALID
OPERATION
exception is raised.
285.7.19 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 character set.
This procedure is equivalent to the PUT_NCHAR Procedure, except that the line separator is appended to the written text. See also PUT_LINE Procedure.
Syntax
UTL_FILE.PUT_LINE_NCHAR ( file IN FILE_TYPE, buffer IN NVARCHAR2);
Parameters
Table 285-23 PUT_LINE_NCHAR Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Text buffer that contains the lines to be written to the file |
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
Usage Notes
-
The maximum size of the
buffer
parameter is 32767 bytes unless you specify a smaller size inFOPEN
. If unspecified, Oracle supplies a default value of 1024. The sum of all sequentialPUT
calls cannot exceed 32767 without intermediate buffer flushes. -
If file is opened for byte mode operations, then the
INVALID
OPERATION
exception is raised.
285.7.20 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 character set. The file must be opened in the national character set mode. The text string will be written in the UTF8 character set. The expected buffer datatype is NVARCHAR2
. If a variable of another datatype is specified, PL/SQL will perform implicit conversion to NVARCHAR2
before writing the text.
Syntax
UTL_FILE.PUT_NCHAR ( file IN FILE_TYPE, buffer IN NVARCHAR2);
Parameters
Table 285-24 PUT_NCHAR Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Buffer that contains the text to be written to the file. User must have opened the file using mode |
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
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.
Related Topics
285.7.21 PUTF Procedure
This procedure is a formatted PUT
procedure.
It works like a limited printf
().
Syntax
UTL_FILE.PUTF ( file IN FILE_TYPE, format IN VARCHAR2, [arg1 IN VARCHAR2 DEFAULT NULL, . . . arg5 IN VARCHAR2 DEFAULT NULL]);
Parameters
Table 285-25 PUTF Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Format string that can contain text as well as the formatting characters |
|
From one to five operational argument strings. Argument strings are substituted, in order, for the If there are more formatters in the format parameter string than there are arguments, then an empty string is substituted for each |
Usage Notes
-
If file is opened for byte mode operations, then the
INVALID
OPERATION
exception is raised. -
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.
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
Examples
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.
Related Topics
285.7.22 PUTF_NCHAR Procedure
This procedure is a formatted version of a PUT_NCHAR Procedure.
Using PUTF_NCHAR
, you can write a text file in Unicode instead of in the database character set. It accepts a format string with formatting elements \n
and %s
, and up to five arguments to be substituted for consecutive instances of %s
in the format string. The expected datatype of the format string and the arguments is NVARCHAR2
.
If variables of another datatype are specified, PL/SQL will perform implicit conversion to NVARCHAR2
before formatting the text. Formatted text is written in the UTF8 character set to the file identified by the file handle. The file must be opened in the national character set mode.
Syntax
UTL_FILE.PUTF_NCHAR ( file IN FILE_TYPE, format IN NVARCHAR2, [arg1 IN NVARCHAR2 DEFAULT NULL, . . . arg5 IN NVARCHAR2 DEFAULT NULL]);
Parameters
Table 285-26 PUTF_NCHAR Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Format string that can contain text as well as the formatting characters |
|
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. |
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
Usage Notes
-
The maximum size of the
buffer
parameter is 32767 bytes unless you specify a smaller size inFOPEN
. If unspecified, Oracle supplies a default value of 1024. The sum of all sequentialPUT
calls cannot exceed 32767 without intermediate buffer flushes. -
If file is opened for byte mode operations, then the
INVALID
OPERATION
exception is raised.
Related Topics
285.7.23 PUT_RAW Procedure
This procedure accepts as input a RAW
data value and writes the value to the output buffer.
Syntax
UTL_FILE.PUT_RAW ( file IN UTL_FILE.FILE_TYPE, buffer IN RAW, autoflush IN BOOLEAN DEFAULT FALSE);
Parameters
Table 285-27 PUT_RAW Procedure Parameters
Parameters | Description |
---|---|
|
File handle |
|
The |
|
If |
Exceptions
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
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.