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.

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

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.

UTL_FILE Examples

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;

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;

FCLOSE Procedure

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

Syntax

UTL_FILE.FCLOSE (

   file IN OUT FILE_TYPE);

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;

FCOPY Procedure

This procedure copies a contiguous portion of a file to a newly created file.

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

FOPEN Function

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

UTL_FILE.FOPEN (

   location     IN VARCHAR2,

   filename     IN VARCHAR2,

   open_mode    IN VARCHAR2,

   max_linesize IN BINARY_INTEGER DEFAULT 1024)

  RETURN FILE_TYPE;

FREMOVE Procedure

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

Syntax

UTL_FILE.FREMOVE (

   location IN VARCHAR2,

   filename IN VARCHAR2);

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

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

IS_OPEN Function

This function tests a file handle to see if it identifies an open file.

UTL_FILE.IS_OPEN (

   file  IN FILE_TYPE)

  RETURN BOOLEAN;

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

UTL_FILE.PUT (

   file      IN FILE_TYPE,

   buffer    IN VARCHAR2); 

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.

UTL_FILE.PUT_LINE (

   file      IN FILE_TYPE,

   buffer    IN VARCHAR2,

   autoflush IN BOOLEAN DEFAULT FALSE); 

PUT_RAW Procedure

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

UTL_FILE.PUT_RAW (

   file          IN    UTL_FILE.FILE_TYPE,

   buffer        IN    RAW, 

   autoflush     IN    BOOLEAN DEFAULT FALSE);