11 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:
-
-
Security model
-
Operational notes
-
Rules and limits
-
Exceptions
-
Examples
-
-
-
Record types
-
Using UTL_FILE
Security Model
UTL_FILE
is limited to the directory timesten_home
/plsql/utl_file_temp
.
Access does not extend to subdirectories of this directory. In addition, access is subject to file system permission checking. The instance administrator can grant UTL_FILE
access to specific users as desired. Users can reference this UTL_FILE
directory by using the string 'UTL_FILE_TEMP' for the location parameter in UTL_FILE
subprograms. This predefined string is used in the same way as directory object names in Oracle Database.
You cannot use UTL_FILE
with a link, which could be used to circumvent desired access limitations. Specifying a link as the file name causes FOPEN
to fail with an error.
For TimesTen direct connections, the application owner is owner of the file. For client/server connections, the server owner is owner of the file.
UTL_FILE_DIR
access is not supported in TimesTen.
Tip:
-
Users do not have execute permission on
UTL_FILE
by default. To useUTL_FILE
in TimesTen, anADMIN
user or instance administrator must explicitlyGRANT EXECUTE
permission on it, such as in the following example:GRANT EXECUTE ON SYS.UTL_FILE TO scott;
-
The privileges needed to access files are operating system specific.
UTL_FILE
privileges give you read and write access to all files within theUTL_FILE
directory, but not in subdirectories. -
Attempting to apply invalid
UTL_FILE
options results in unpredictable behavior.
Operational Notes
UTL_FILE
is limited to the directory
timesten_home/plsql/utl_file_temp
. Access does not extend to
subdirectories of this directory. In addition, access is subject to file system
permission checking. The instance administrator can grant UTL_FILE
access to specific users as desired. Users can reference this
UTL_FILE
directory by using the string
'UTL_FILE_TEMP'
for the location parameter in
UTL_FILE
subprograms. This predefined string is used in the
same way as directory object names in Oracle Database.
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
utl_file_temp
directory. Together, the file location and name
must represent a valid file name on the system, and the directory must be
accessible. Any subdirectories of utl_file_temp
are not
accessible.
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 GET_LINE
specifies the requested number of bytes of character data. The number of bytes actually returned to the user is the least of the following:
-
GET_LINE
len
parameter value -
Number of bytes until the next line terminator character
-
The
max_linesize
parameter value specified byFOPEN
The FOPEN
max_linesize
parameter must be a number in the range 1 and 32767. If unspecified, TimesTen supplies a default value of 1024. The GET_LINE
len
parameter must be a number in the range 1 and 32767. If unspecified, TimesTen supplies the default value of max_linesize
. If max_linesize
and len
are defined to be different values, then the lesser value takes precedence.
When data encoded in one character set is read and Globalization Support is informed (such as through 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 Linux or 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, 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, call FCLOSE
to complete any output and free resources associated with the file.
Exceptions
This section describes exceptions that are thrown by UTL_FILE
subprograms.
Note:
In addition to the exceptions listed here, procedures and functions in UTL_FILE
can raise predefined PL/SQL exceptions such as NO_DATA_FOUND
or VALUE_ERROR
. Refer to Predefined Exceptions in Oracle Database PL/SQL Language Reference for information about those.
Table 11-1 UTL_FILE package exceptions
Exception Name | Description |
---|---|
|
Permission to access to the file location is denied. |
|
A file is opened using |
|
Requested file delete operation failed. |
|
Requested operation failed because the file is open. |
|
There was an unspecified PL/SQL error. |
|
File handle is invalid. |
|
The |
|
The |
|
The |
|
Caused by one of the following:
|
|
File could not be opened or operated on as requested. |
|
File location or name is invalid. |
|
Length mismatch for |
|
Operating system error occurred during the read operation. |
|
Requested file rename operation failed. |
|
Operating system error occurred during the write operation. |
Examples
Example 1: GET_LINE
This example reads from a file using the GET_LINE
procedure.
DECLARE
V1 VARCHAR2(32767);
F1 UTL_FILE.FILE_TYPE;
BEGIN
-- In this example MAX_LINESIZE is less than GET_LINE's length request
-- so number of bytes returned is 256 or less if a line terminator is seen.
F1 := UTL_FILE.FOPEN('UTL_FILE_TEMP','u12345.tmp','R',256);
UTL_FILE.GET_LINE(F1,V1,32767);
DBMS_OUTPUT.PUT_LINE('Get line: ' || V1);
UTL_FILE.FCLOSE(F1);
-- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024,
-- so number of bytes returned is 1024 or less if line terminator is seen.
F1 := UTL_FILE.FOPEN('UTL_FILE_TEMP','u12345.tmp','R');
UTL_FILE.GET_LINE(F1,V1,32767);
DBMS_OUTPUT.PUT_LINE('Get line: ' || V1);
UTL_FILE.FCLOSE(F1);
-- GET_LINE doesn't specify a number of bytes, so it defaults to
-- same value as FOPEN's MAX_LINESIZE which is NULL and defaults to 1024.
-- So number of bytes returned is 1024 or less if line terminator is seen.
F1 := UTL_FILE.FOPEN('UTL_FILE_TEMP','u12345.tmp','R');
UTL_FILE.GET_LINE(F1,V1);
DBMS_OUTPUT.PUT_LINE('Get line: ' || V1);
UTL_FILE.FCLOSE(F1);
END;
Consider the following test file, u12345.tmp
, in the utl_file_temp
directory:
This is line 1.
This is line 2.
This is line 3.
This is line 4.
This is line 5.
The example results in the following output, repeatedly getting the first line only:
Get line: This is line 1.
Get line: This is line 1.
Get line: This is line 1.
PL/SQL procedure successfully completed.
Example 2: PUTF
This appends content to the end of a file using the PUTF
procedure.
declare
handle utl_file.file_type;
my_world varchar2(4) := 'Zork';
begin
handle := utl_file.fopen('UTL_FILE_TEMP','u12345.tmp','a');
utl_file.putf(handle, '\nHello, world!\nI come from %s with %s.\n', my_world,
'greetings for all earthlings');
utl_file.fflush(handle);
utl_file.fclose(handle);
end;
This appends the following to file u12345.tmp
in the utl_file_temp
directory.
Hello, world!
I come from Zork with greetings for all earthlings.
Example 3: GET_RAW
This procedure gets raw data from a specified file using the GET_RAW
procedure. It exits when it reaches the end of the data, through its handling of NO_DATA_FOUND
in the EXCEPTION
processing.
CREATE OR REPLACE PROCEDURE getraw(n IN VARCHAR2) IS
h UTL_FILE.FILE_TYPE;
Buf RAW(32767);
Amnt CONSTANT BINARY_INTEGER := 32767;
BEGIN
h := UTL_FILE.FOPEN('UTL_FILE_TEMP', n, 'r', 32767);
LOOP
BEGIN
UTL_FILE.GET_RAW(h, Buf, Amnt);
-- Do something with this chunk
DBMS_OUTPUT.PUT_LINE('This is the raw data:');
DBMS_OUTPUT.PUT_LINE(Buf);
EXCEPTION WHEN No_Data_Found THEN
EXIT;
END;
END LOOP;
UTL_FILE.FCLOSE (h);
END;
Consider the following content in file u12345.tmp
in the utl_file_temp
directory:
hello world!
The example produces output as follows:
Command> begin
getraw('u12345.tmp');
end;
/
This is the raw data:
68656C6C6F20776F726C64210A
PL/SQL procedure successfully completed.
Data Structures
The UTL_FILE
package defines the following record type.
Record types
FILE_TYPE 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 11-2 FILE_TYPE Fields
Field | Description |
---|---|
|
Indicates the internal file handle number (numeric value). |
|
Indicates whether the file is a |
|
Indicates whether the file was open as a binary file or as a text file. |
Tip:
Oracle Database 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 indeterminate outcomes.
Note:
-
The
PLS_INTEGER
andBINARY_INTEGER
data types are identical. This document usesBINARY_INTEGER
to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples. -
The
INTEGER
andNUMBER(38)
data types are also identical. This document usesINTEGER
throughout.
UTL_FILE Subprograms
Table 11-3 summarizes the UTL_FILE
subprograms, followed by a full description of each subprogram.
Table 11-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 file. |
|
Returns the current relative offset position (in bytes) within a file, in bytes. |
|
Opens a file for input or output. |
|
Opens a file in Unicode for input or output. |
|
Deletes a file if 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. |
|
Accepts as input a |
|
This is equivalent to |
|
This is equivalent to |
FCLOSE Procedure
This procedure closes an open file identified by a file handle.
Syntax
UTL_FILE.FCLOSE (
file IN OUT UTL_FILE.FILE_TYPE);
Parameters
Table 11-4 FCLOSE Procedure Parameters
Parameter | Description |
---|---|
|
Active file handle returned by an |
Exceptions
Refer to Exceptions for information about these exceptions.
INVALID_FILEHANDLE
WRITE_ERROR
If there is buffered data yet to be written when FCLOSE
runs, you may receive WRITE_ERROR
when closing a file.
Examples
See Examples.
FCLOSE_ALL Procedure
This procedure closes all open file handles for the session. This is useful as an emergency cleanup procedure, such as after a PL/SQL program exits on an exception.
Syntax
UTL_FILE.FCLOSE_ALL;
Usage Notes
FCLOSE_ALL
does not alter the state of the open file handles held by the user. Therefore, 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
.
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 11-5 FCOPY Procedure Parameters
Parameters | Description |
---|---|
|
Directory location of the source file |
|
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
Refer to Exceptions.
INVALID_FILENAME
INVALID_PATH
INVALID_OPERATION
INVALID_OFFSET
READ_ERROR
WRITE_ERROR
FFLUSH Procedure
FFLUSH
physically writes pending data to the file identified by the file handle.
Typically, data 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 UTL_FILE.FILE_TYPE);
Parameters
Table 11-6 FFLUSH Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
Examples
See Examples.
FGETATTR Procedure
This procedure reads and returns the attributes of a 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 11-7 FGETATTR Procedure Parameters
Parameters | Description |
---|---|
|
Location of the source file |
|
Name of the file to be examined |
|
A |
|
Length of the file in bytes, or |
|
File system block size in bytes, or |
Exceptions
Refer to Exceptions.
INVALID_PATH
INVALID_FILENAME
INVALID_OPERATION
READ_ERROR
ACCESS_DENIED
FGETPOS Function
This function returns the current relative offset position within a file, in bytes.
Syntax
UTL_FILE.FGETPOS (
file IN utl_file.file_type)
RETURN BINARY_INTEGER;
Parameters
Table 11-8 FGETPOS Function Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
Return Value
The relative offset position for an open file, in bytes, or 0 for the beginning of the file
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
An INVALID_FILEHANDLE
exception is raised if the file is not open. An INVALID_OPERATION
exception is raised if the file was opened for byte mode operations.
FOPEN Function
This function opens a file. You can specify the maximum line size and have a maximum of 50 files open simultaneously.
Also see 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 utl_file.file_type;
Parameters
Table 11-9 FOPEN Function Parameters
Parameter | Description |
---|---|
|
Directory location of file |
|
File name, including extension (file type), without directory path If a directory path is given as a part of the file name, it is ignored by |
|
Mode in which the file was opened:
If you try to open a file specifying ' |
|
Maximum number of characters for each line, including the newline character, for this file The minimum value is 1 and the maximum is 32767. If this is unspecified, TimesTen supplies a default value of 1024. |
Return Value
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.
Usage 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 utl_file_temp
directory. Together, the file
location and name must represent a valid file name on the system, and the directory must
be accessible. Any subdirectories of utl_file_temp
are not
accessible.
Examples
See Examples.
FOPEN_NCHAR Function
This function opens a file in national character set mode 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 character set.
Even though the contents of an NVARCHAR2
buffer may be AL16UTF16
or UTF-8 (depending on the national character set of the database), the contents of the file are always read and written in UTF-8. UTL_FILE
converts between UTF-8 and AL16UTF16
as necessary.
Also see 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 utl_file.file_type;
Parameters
Table 11-10 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 The minimum value is 1 and the maximum is 32767. If this is unspecified, TimesTen supplies a default value of 1024. |
Return Value
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.
FREMOVE Procedure
This procedure deletes a file if you have sufficient privileges.
Syntax
UTL_FILE.FREMOVE (
location IN VARCHAR2,
filename IN VARCHAR2);
Parameters
Table 11-11 FREMOVE Procedure Parameters
Parameters | Description |
---|---|
|
Directory location of the file |
|
Name of the file to be deleted |
Usage Notes
This procedure does not verify privileges before deleting a file. The operating system verifies file and directory permissions.
Exceptions
Refer to Exceptions.
INVALID_PATH
INVALID_FILENAME
INVALID_OPERATION
ACCESS_DENIED
DELETE_FAILED
FRENAME Procedure
This procedure renames an existing file.
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 11-12 FRENAME Procedure Parameters
Parameters | Description |
---|---|
|
Directory location of the source file |
|
Source file to be renamed |
|
Destination directory of the destination file |
|
New name of the file |
|
Whether it is permissible to overwrite an existing file in the destination directory (default |
Usage Notes
Permission on both the source and destination directories must be granted.
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 BINARY_INTEGER DEFAULT NULL,
relative_offset IN BINARY_INTEGER DEFAULT NULL);
Parameters
Table 11-13 FSEEK Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Absolute location to which to seek, in bytes (default |
|
Number of bytes to seek forward or backward Use a positive integer to seek forward, a negative integer to see backward, or 0 for the current position. Default is |
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 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.
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
INVALID_OFFSET
INVALID_OPERATION
is raised if the file was opened for byte-mode operations. INVALID_OFFSET
is raised if the end of the file is reached before the number of bytes specified.
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 UTL_FILE.FILE_TYPE,
buffer OUT VARCHAR2,
len IN BINARY_INTEGER DEFAULT NULL);
Parameters
Table 11-14 GET_LINE Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Data buffer to receive the line read from the file |
|
Number of bytes read from the file If |
Usage Notes
-
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 inFOPEN
. -
If unspecified, TimesTen supplies a default value of 1024. Also see GET_LINE_NCHAR Procedure.
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
CHARSETMISMATCH
NO_DATA_FOUND
VALUE_ERROR
INVALID_OPERATION
is thrown if the file was not opened for read mode (mode r
) or was opened for byte-mode operations. CHARSETMISMATCH
is thrown if FOPEN_NCHAR
was used instead of FOPEN
to open the file. NO_DATA_FOUND
is thrown if no text was read due to end of file. VALUE_ERROR
is thrown if the line does not fit into the buffer. (NO_DATA_FOUND
and VALUE_ERROR
are predefined PL/SQL exceptions.)
Examples
See Examples.
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 UTF-8 character set. The expected buffer data type is NVARCHAR2
. If a variable of another data type such as NCHAR
or VARCHAR2
is specified, PL/SQL performs standard implicit conversion from NVARCHAR2
after the text is read.
Also see GET_LINE Procedure.
Syntax
UTL_FILE.GET_LINE_NCHAR (
file IN UTL_FILE.FILE_TYPE,
buffer OUT NVARCHAR2,
len IN BINARY_INTEGER DEFAULT NULL);
Parameters
Table 11-15 GET_LINE_NCHAR 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 If |
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
CHARSETMISMATCH
NO_DATA_FOUND
VALUE_ERROR
INVALID_OPERATION
is thrown if the file was not opened for read mode (mode r
) or was opened for byte-mode operations. NO_DATA_FOUND
is thrown if no text was read due to end of file. VALUE_ERROR
is thrown if the line does not fit into the buffer. CHARSETMISMATCH
is thrown if the file was opened by FOPEN
instead of FOPEN_NCHAR
. (NO_DATA_FOUND
and VALUE_ERROR
are predefined PL/SQL exceptions.)
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. It ignores line terminators.
Syntax
UTL_FILE.GET_RAW (
file IN utl_file.file_type,
buffer OUT NOCOPY RAW,
len IN BINARY_INTEGER DEFAULT NULL);
Parameters
Table 11-16 GET_RAW Function Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
The |
|
Number of bytes read from the file If |
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
LENGTH_MISMATCH
NO_DATA_FOUND
(NO_DATA_FOUND
is a predefined PL/SQL exception.)
Examples
See Examples.
IS_OPEN Function
This function tests a file handle to see if it identifies an open file. It reports only whether a file handle represents a file that has been opened, but not yet closed. It does not guarantee you can use the file without error.
Syntax
UTL_FILE.IS_OPEN (
file IN UTL_FILE.FILE_TYPE)
RETURN BOOLEAN;
Parameters
Table 11-17 IS_OPEN Function Parameters
Parameter | Description |
---|---|
|
Active file handle returned by an |
Return Value
TRUE
if the file is open, or FALSE
if not
NEW_LINE Procedure
This procedure writes one or more line terminators to the file identified by the input file handle. This procedure is distinct from PUT
because the line terminator is a platform-specific character or sequence of characters.
Syntax
UTL_FILE.NEW_LINE (
file IN UTL_FILE.FILE_TYPE,
lines IN BINARY_INTEGER := 1);
Parameters
Table 11-18 NEW_LINE Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Number of line terminators to be written to the file |
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 PUT_LINE
to write a complete line with a line terminator. Also see PUT_NCHAR Procedure.
Syntax
UTL_FILE.PUT (
file IN UTL_FILE.FILE_TYPE,
buffer IN VARCHAR2);
Parameters
Table 11-19 PUT Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an The file must be open for writing (mode |
|
Buffer that contains the text 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, TimesTen supplies a default value of 1024. The sum of all sequential PUT
calls cannot exceed 32767 without intermediate buffer flushes.
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
CHARSETMISMATCH
INVALID_OPERATION
is thrown if the file was not opened using mode w
or a
(write or append). CHARSETMISMATCH
is thrown if FOPEN_NCHAR
was used instead of FOPEN
to open the file.
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. Also see PUT_LINE_NCHAR Procedure.
Syntax
UTL_FILE.PUT_LINE (
file IN UTL_FILE.FILE_TYPE,
buffer IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE);
Parameters
Table 11-20 PUT_LINE Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Text buffer that contains the lines to be written to the file |
|
Flag for flushing the buffer to the file system 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, TimesTen supplies a default value of 1024. The sum of all sequential PUT
calls cannot exceed 32767 without intermediate buffer flushes.
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
CHARSETMISMATCH
INVALID_OPERATION
is thrown if the file was opened for byte-mode operations. CHARSETMISMATCH
is thrown if FOPEN_NCHAR
was used instead of FOPEN
to open the file.
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. Also see PUT_LINE Procedure.
Syntax
UTL_FILE.PUT_LINE_NCHAR (
file IN UTL_FILE.FILE_TYPE,
buffer IN NVARCHAR2);
Parameters
Table 11-21 PUT_LINE_NCHAR Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an The file must be open for writing (mode |
|
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, TimesTen supplies a default value of 1024. The sum of all sequential PUT
calls cannot exceed 32767 without intermediate buffer flushes.
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
CHARSETMISMATCH
INVALID_OPERATION
is thrown if the file was opened for byte-mode operations. CHARSETMISMATCH
is thrown if FOPEN
was used instead of FOPEN_NCHAR
to open the file.
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 is written in the UTF-8 character set. The expected buffer data type is NVARCHAR2
. If a variable of another data type is specified, PL/SQL performs implicit conversion to NVARCHAR2
before writing the text.
Also see PUT Procedure.
Syntax
UTL_FILE.PUT_NCHAR (
file IN UTL_FILE.FILE_TYPE,
buffer IN NVARCHAR2);
Parameters
Table 11-22 PUT_NCHAR Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Buffer that contains the text 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, TimesTen supplies a default value of 1024. The sum of all sequential PUT
calls cannot exceed 32767 without intermediate buffer flushes.
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
CHARSETMISMATCH
INVALID_OPERATION
is thrown if the file was not opened using mode w
or a
(write or append). CHARSETMISMATCH
is thrown if the file was opened by FOPEN
instead of FOPEN_NCHAR
.
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 11-23 PUT_RAW Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
The |
|
Flag to perform a flush after writing the value to the output buffer (default is |
Usage Notes
You can request an automatic flush of the buffer by setting autoflush
to TRUE
.
The maximum size of the buffer
parameter is 32767 bytes unless you specify a smaller size in FOPEN
. If unspecified, TimesTen 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()
.
Also see PUTF_NCHAR Procedure.
Syntax
UTL_FILE.PUTF (
file IN UTL_FILE.FILE_TYPE,
format IN VARCHAR2,
[arg1 IN VARCHAR2 DEFAULT NULL,
. . .
arg5 IN VARCHAR2 DEFAULT NULL]);
Parameters
Table 11-24 PUTF Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an |
|
Format string that can contain text and the formatting characters |
|
From one to five operational argument strings Argument strings are substituted, in order, for the |
Usage Notes
The format string can contain any text, but the character sequences %s
and \n
have special meaning.
Character sequence | Meaning |
---|---|
|
Substitute this sequence with the string value of the next argument in the argument list. |
|
Substitute with the appropriate platform-specific line terminator. |
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
CHARSETMISMATCH
INVALID_OPERATION
is thrown if the file was opened for byte-mode operations. CHARSETMISMATCH
is thrown if FOPEN_NCHAR
was used instead of FOPEN
to open the file.
Examples
See Examples.
PUTF_NCHAR Procedure
Using PUTF_NCHAR
, you can write a text file in Unicode instead of in the database character set.
This procedure is the formatted version of the PUT_NCHAR Procedure.
It accepts a format string with formatting elements \n
and %s
, and up to five arguments to be substituted for consecutive occurrences of %s
in the format string. The expected data type of the format string and the arguments is NVARCHAR2
.
If variables of another data type are specified, PL/SQL performs implicit conversion to NVARCHAR2
before formatting the text. Formatted text is written in the UTF-8 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 UTL_FILE.FILE_TYPE,
format IN NVARCHAR2,
[arg1 IN NVARCHAR2 DEFAULT NULL,
. . .
arg5 IN NVARCHAR2 DEFAULT NULL]);
Parameters
Table 11-25 PUTF_NCHAR Procedure Parameters
Parameters | Description |
---|---|
|
Active file handle returned by an The file must be open for reading (mode |
|
Format string that can contain text and the format characters |
|
From one to five operational argument strings Argument strings are substituted, in order, for the |
Usage Notes
The maximum size of the buffer
parameter is 32767 bytes unless you specify a smaller size in FOPEN
. If unspecified, TimesTen supplies a default value of 1024. The sum of all sequential PUT
calls cannot exceed 32767 without intermediate buffer flushes.
Exceptions
Refer to Exceptions.
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR
CHARSETMISMATCH
INVALID_OPERATION
is thrown if the file was opened for byte-mode operations. CHARSETMISMATCH
is thrown if the file was opened by FOPEN
instead of FOPEN_NCHAR
.