UTL_FILE Operation and Functions

To Read About:

How UTL_FILE Package used for FILE I/O Operations: Click here.

UTL_FILE common Errors and how to resolve them: Click here.

EXCEPTION Related to UTL_FILE: Click here.

UTL_FILE.FOPEN

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

Syntax

UTL_FILE.FOPEN (

Location     IN VARCHAR2,

Filename     IN VARCHAR2,

Open_Mode    IN VARCHAR2,

Max_Linesize IN BINARY_INTEGER)

RETURN File_Type;

Location: Directory location of file. This string is a directory object name and is case sensitive. The default is uppercase. Read privileges must be granted on this directory object for the UTL_FILE user to run FOPEN.

Filename: File name, including extension (file type), without directory path. If a directory path is given as a part of the filename, it is ignored by FOPEN. On Unix, the filename cannot end with /.

Open_Mode: Specifies how the file is opened. Modes include:

·       r -- read text

·       w -- write text

·       a -- append text

·       rb -- read byte mode

·       wb -- write byte mode

·       ab -- append byte mode

If you try to open a file specifying 'a' or 'ab' for open_mode but the file does not exist, the file is created in write mode.

Max_Linesize: Maximum number of characters for each line, including the newline character, for this file. (minimum value 1, maximum value 32767). If unspecified, Oracle supplies a default value of 1024.

Return Values: File_Type Handle to open file.

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.

Usage Notes

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

Exceptions

·       INVALID_PATH: File Location or name was invalid.
 

·       INVALID_MODE: The Open_Mode string was invalid.
 

·       INVALID_OPERATION: File could not be opened as requested.
 

·       INVALID_MAXLINESIZE: Specified Max_Linesize is too large or too small.

UTL_FILE.FOPEN_NCHAR (UTLPI)

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

Syntax

UTL_FILE.FOPEN_NCHAR (

   Location     IN VARCHAR2,

   Filename     IN VARCHAR2,

   Open_Mode    IN VARCHAR2,

  Max_Linesize IN BINARY_INTEGER)

  RETURN file_type;

Location	Directory location of file.
Filename	File name (including extension).
Open_Mode	Open mode (r,w,a,rb,wb,ab).
Max_Linesize	Maximum number of characters per line, including the newline character, for this file. (minimum value 1, maximum value 32767).

UTL_FILE.FCLOSE

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

Syntax

UTL_FILE.FCLOSE (File IN OUT FILE_TYPE);

File	Active file handle returned by an FOPEN or FOPEN_NCHAR call.

Exceptions

·       WRITE_ERROR
 

·       INVALID_FILEHANDLE

UTL_FILE.FCLOSE_ALL

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

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.

Syntax

UTL_FILE.FCLOSE_ALL;

Exceptions

·       WRITE_ERROR

UTL_FILE.FCOPY

This function 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 (

   Location   IN VARCHAR2,

   Filename   IN VARCHAR2,

   Dest_Dir   IN VARCHAR2,

   Dest_File  IN VARCHAR2,

   Start_Line IN PLS_INTEGER DEFAULT 1,

   End_Line   IN PLS_INTEGER DEFAULT NULL);

Location (IN)	The directory location of the source file, a DIRECTORY_NAME from the ALL_DIRECTORIES view (case sensitive)
Filename (IN)	The source file to be copied
Dest_Dir (IN)	The destination directory where the destination file is created.
Dest_File (N)	The destination file created from the source file.
Start_Line (IN)	The line number at which to begin copying. The default is 1 for the first line.
End_Line (IN)	The line number at which to stop copying. The default is NULL, signifying end of file.

 

UTL_FILE.FFLUSH

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

Invalid_Maxlinesize  EXCEPTION;

File	Active file handle returned by an FOPEN or FOPEN_NCHAR call.

Exceptions

·       INVALID_FILEHANDLE
 

·       INVALID_OPERATION
 

·       WRITE_ERROR

UTL_FILE.FREMOVE

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

Syntax

UTL_FILE.FREMOVE (

   location IN VARCHAR2,

   filename IN VARCHAR2);

Location (IN)	The directory location of the file, a DIRECTORY_NAME from ALL_DIRECTORIES (case sensitive)
Filename (IN)	The name of the file to be deleted

Notes

The FREMOVE function does not verify privileges prior to deleting the file. The O/S verifies file and directory permissions. An exception is returned on failure.

UTL_FILE.NEW_LINE

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

Syntax

UTL_FILE.NEW_LINE (

   file     IN FILE_TYPE,

   lines    IN NATURAL := 1);

File	Active file handle returned by an FOPEN or FOPEN_NCHAR call.
Lines	Number of line terminators to be written to the file.

Exceptions

·       INVALID_FILEHANDLE
 

·       INVALID_OPERATION
 

·       WRITE_ERROR

UTL_FILE.IS_OPEN

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;

File	Active file handle returned by an FOPEN or FOPEN_NCHAR call.

Returns

TRUE or FALSE

UTL_FILE.PUT

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

The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. The default is approximately 1000 bytes, depending on your platform. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.

Syntax

UTL_FILE.PUT (File      IN FILE_TYPE, Buffer    IN VARCHAR2);

File	Active file handle returned by an FOPEN_NCHAR call. The file must be open for reading (mode r). If the file is opened by FOPEN instead of FOPEN_NCHAR, a CHARSETMISMATCH exception is raised.
Buffer	Buffer that contains the text to be written to the file. You must have opened the file using mode w or mode a; otherwise, an INVALID_OPERATION exception is raised.

Exceptions

·       INVALID_FILEHANDLE
 

·       INVALID_OPERATION
 

·       WRITE_ERROR

UTL_FILE.PUT_LINE

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

The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. The default is approximately 1000 bytes, depending on your platform. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.

See also PUT_LINE_NCHAR Procedure.

Syntax

UTL_FILE.PUT_LINE (

 File      IN FILE_TYPE,

 Buffer    IN VARCHAR2,

 Autoflush IN BOOLEAN DEFAULT FALSE);

File	Active file handle returned by an FOPEN call.
Buffer	Text buffer that contains the lines to be written to the file.
Autoflush	Flushes the buffer to disk after the WRITE.

Exceptions

·       INVALID_FILEHANDLE
 

·       INVALID_OPERATION
 

·       WRITE_ERROR