Skip Headers
Oracle® TimesTen In-Memory Database PL/SQL Packages Reference
11g Release 2 (11.2.2)

E21645-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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:


Using UTL_FILE


Security model

In TimesTen 11.2.x releases, UTL_FILE is limited to the directory install_dir/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.

Important:

  • Users do not have execute permission on UTL_FILE by default. To use UTL_FILE in TimesTen, an ADMIN user or instance administrator must explicitly GRANT 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 the UTL_FILE directory, but not in subdirectories.

  • Attempting to apply invalid UTL_FILE options results in unpredictable behavior.


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 utl_file_temp directory. Together, the file location and name must represent a legal 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:

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 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

ACCESS_DENIED

Permission to access to the file location is denied.

CHARSETMISMATCH

A file is opened using FOPEN_NCHAR, but later I/O operations use non-NCHAR procedures such as PUTF or GET_LINE. Or a file is opened using FOPEN, but later I/O operations use NCHAR functions such as PUTF_NCHAR or GET_LINE_NCHAR.

DELETE_FAILED

Requested file delete operation failed.

FILE_OPEN

Requested operation failed because the file is open.

INTERNAL_ERROR

There was an unspecified PL/SQL error.

INVALID_FILEHANDLE

File handle is invalid.

INVALID_FILENAME

The filename parameter is invalid.

INVALID_MAXLINESIZE

The max_linesize value for FOPEN is out of range. It should be within the range 1 to 32767.

INVALID_MODE

The open_mode parameter in FOPEN is invalid.

INVALID_OFFSET

Caused by one of the following:

  • ABSOLUTE_OFFSET is NULL and RELATIVE_OFFSET is NULL.

  • ABSOLUTE_OFFSET is less than 0.

  • Either offset caused a seek past the end of the file.

INVALID_OPERATION

File could not be opened or operated on as requested.

INVALID_PATH

File location or name is invalid.

LENGTH_MISMATCH

Length mismatch for CHAR or RAW data.

READ_ERROR

Operating system error occurred during the read operation.

RENAME_FAILED

Requested file rename operation failed.

WRITE_ERROR

Operating system error occurred during the write operation.



Examples

Example 1

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

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

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 a 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

id

Indicates the internal file handle number (numeric value).

datatype

Indicates whether the file is a CHAR file, NCHAR file, or other (binary).

byte_mode

Indicates whether the file was open as a binary file or as a text file.


Important:

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.

Notes:

  • The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_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 and NUMBER(38) data types are also identical. This document uses INTEGER throughout.


Summary of UTL_FILE subprograms

Table 11-3 UTL_FILE Subprograms

Subprogram Description

FCLOSE procedure

Closes a file.

FCLOSE_ALL procedure

Closes all open file handles.

FCOPY procedure

Copies a contiguous portion of a file to a newly created file.

FFLUSH procedure

Physically writes all pending output to a file.

FGETATTR procedure

Reads and returns the attributes of a disk file.

FGETPOS function

Returns the current relative offset position (in bytes) within a file, in bytes.

FOPEN function

Opens a file for input or output.

FOPEN_NCHAR function

Opens a file in Unicode for input or output.

FREMOVE procedure

Deletes a disk file if you have sufficient privileges.

FRENAME procedure

Renames an existing file to a new name, similar to the UNIX mv function.

FSEEK procedure

Adjusts the file pointer forward or backward within the file by the number of bytes specified.

GET_LINE procedure

Reads text from an open file.

GET_LINE_NCHAR procedure

Reads text in Unicode from an open file.

GET_RAW procedure

Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read.

IS_OPEN function

Determines if a file handle refers to an open file.

NEW_LINE procedure

Writes one or more operating system-specific line terminators to a file.

PUT procedure

Writes a string to a file.

PUT_LINE procedure

Writes a line to a file, and so appends an operating system-specific line terminator.

PUT_LINE_NCHAR procedure

Writes a Unicode line to a file.

PUT_NCHAR procedure

Writes a Unicode string to a file.

PUT_RAW procedure

Accepts as input a RAW data value and writes the value to the output buffer.

PUTF procedure

This is equivalent to PUT but with formatting.

PUTF_NCHAR procedure

This is equivalent to PUT_NCHAR but with formatting.



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

file

Active file handle returned by an FOPEN or FOPEN_NCHAR call


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.

Exceptions

Refer to "Exceptions" for information about this exception.

WRITE_ERROR

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

src_location

Directory location of the source file

src_filename

Source file to be copied

dest_location

Destination directory where the destination file is created

dest_filename

Destination file created from the source file

start_line

Line number at which to begin copying

The default is 1 for the first line.

end_line

Line number at which to stop copying

The default is NULL, signifying end of file.


Exceptions

Refer to "Exceptions" for information about these 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. 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 UTL_FILE.FILE_TYPE);

Parameters

Table 11-6 FFLUSH procedure parameters

Parameters Description

file

Active file handle returned by an FOPEN or FOPEN_NCHAR call


Exceptions

Refer to "Exceptions" for information about these exceptions.

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

Examples

See "Examples".


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 11-7 FGETATTR procedure parameters

Parameters Description

location

Location of the source file

filename

Name of the file to be examined

fexists

A BOOLEAN for whether the file exists

file_length

Length of the file in bytes, or NULL if file does not exist

block_size

File system block size in bytes, or NULL if file does not exist


Exceptions

Refer to "Exceptions" for information about these 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

file

Active file handle returned by an FOPEN or FOPEN_NCHAR call


Return value

The relative offset position for an open file, in bytes, or 0 for the beginning of the file

Exceptions

Refer to "Exceptions" for information about these 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

location

Directory location of file

filename

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 FOPEN. On UNIX, the file name cannot end with a slash, "/".

open_mode

Mode in which the file was opened:

  • r - Read text mode

  • w - Write text mode

  • a - Append text mode

  • 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

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 legal file name on the system, and the directory must be accessible. Any subdirectories of utl_file_temp are not accessible.

Exceptions

Refer to "Exceptions" for information about these exceptions.

INVALID_PATH
INVALID_MODE
INVALID_OPERATION
INVALID_MAXLINESIZE

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

location

Directory location of file

filename

File name, including extension

open_mode

Open mode: r, w, a, rb, wb, or ab (as documented for FOPEN)

max_linesize

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.

Exceptions

Refer to "Exceptions" for information about these exceptions.

INVALID_PATH
INVALID_MODE
INVALID_OPERATION
INVALID_MAXLINESIZE

FREMOVE procedure

This procedure deletes a disk 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

location

Directory location of the file

filename

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" for information about these 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

src_location

Directory location of the source file

src_filename

Source file to be renamed

dest_location

Destination directory of the destination file

dest_filename

New name of the file

overwrite

Whether it is permissible to overwrite an existing file in the destination directory (default FALSE)


Usage notes

Permission on both the source and destination directories must be granted.

Exceptions

Refer to "Exceptions" for information about these exceptions.

INVALID_PATH
INVALID_FILENAME
RENAME_FAILED
ACCESS_DENIED

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

file

Active file handle returned by an FOPEN or FOPEN_NCHAR call

absolute_offset

Absolute location to which to seek, in bytes (default NULL)

relative_offset

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 NULL.


Usage notes

Exceptions

Refer to "Exceptions" for information about these 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

file

Active file handle returned by an FOPEN call

buffer

Data buffer to receive the line read from the file

len

Number of bytes read from the file

If NULL (default), TimesTen supplies the value of max_linesize from FOPEN.


Usage notes

Exceptions

Refer to "Exceptions" for information about these 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

file

Active file handle returned by an FOPEN_NCHAR call

The file must be open for reading (mode r).

buffer

Data buffer to receive the line read from the file

len

The number of bytes read from the file

If NULL (default), TimesTen supplies the value of max_linesize from FOPEN_NCHAR.


Exceptions

Refer to "Exceptions" for information about these 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

file

Active file handle returned by an FOPEN or FOPEN_NCHAR call

buffer

The RAW data

len

Number of bytes read from the file

If NULL (default), len is assumed to be the maximum length of RAW.


Exceptions

Refer to "Exceptions" for information about these 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

file

Active file handle returned by an FOPEN or FOPEN_NCHAR call


Return value

TRUE if the file is open, or FALSE if not

Exceptions

Refer to "Exceptions" for information about this exception.

INVALID_FILEHANDLE

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

file

Active file handle returned by an FOPEN or FOPEN_NCHAR call

lines

Number of line terminators to be written to the file


Exceptions

Refer to "Exceptions" for information about this exception.

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

PUT procedure

PUT writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations. No line terminator is appended by PUT. Use NEW_LINE to terminate the line or 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

file

Active file handle returned by an FOPEN_NCHAR call

The file must be open for writing (mode w).

buffer

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" for information about this exception.

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

file

Active file handle returned by an FOPEN call

buffer

Text buffer that contains the lines to be written to the file

autoflush

Flag for flushing 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, 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" for information about this exception.

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

file

Active file handle returned by an FOPEN_NCHAR call

The file must be open for writing (mode w).

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, 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" for information about this exception.

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

file

Active file handle returned by an FOPEN_NCHAR call

buffer

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" for information about this exception.

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

file

Active file handle returned by an FOPEN or FOPEN_NCHAR call

buffer

The RAW data written to the buffer

autoflush

Flag to perform 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 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.

Exceptions

Refer to "Exceptions" for information about these exceptions.

INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

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

file

Active file handle returned by an FOPEN call

format

Format string that can contain text and 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, 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.

Exceptions

Refer to "Exceptions" for information about these 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

This procedure is the formatted version of the 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 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

file

Active file handle returned by an FOPEN_NCHAR call

The file must be open for reading (mode r).

format

Format string that can contain text and the format characters \n and %s

arg1..arg5

From one to five operational argument strings

Argument strings are substituted, in order, for the %s format characters in the format string. If there are more format characters in the format string than there are arguments, 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, 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" for information about these 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.