UTL_FILE package with example

What is UTL_FILE Package?

• UTL_FILE is an oracle pl/sql package that is supplied to allow PL/SQL to read and create text files in the file system.
• UTL_FILE can only read and create text files. Specifically, it cannot be used to read or create binary files.
• UTL_FILE is an appropriate tool for creating reports in the form of flat file from the database. UTL_FILE is also used for reading files.

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 by UTL_FILE.FOPEN

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.

SQL> CREATE DIRECTORY log_dir AS '/appl/gl/log';

SQL> GRANT READ ON DIRECTORY log_dir TO DBA;

SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user';

SQL> GRANT READ 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('MYDIR', 'MYFILE', '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('MYDIR', 'MYFILE', '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('MYDIR', 'MYFILE', 'R');

  UTL_FILE.GET_LINE(F1, V1);

  UTL_FILE.FCLOSE(F1);

END;

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;

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

invalid_maxlinesize  EXCEPTION;

FOPEN Function

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

Syntex:

UTL_FILE.FOPEN (

   location     IN VARCHAR2,

   filename     IN VARCHAR2,

   open_mode    IN VARCHAR2,

   max_linesize IN BINARY_INTEGER)

  RETURN file_type;

FREMOVE Procedure

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

Syntex:

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.

Syntex:

UTL_FILE.FRENAME (

   location  IN VARCHAR2,

   filename  IN VARCHAR2,

   dest_dir  IN VARCHAR2,

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

Syntex:

UTL_FILE.GET_LINE (

   file        IN  FILE_TYPE,

   buffer      OUT VARCHAR2,

   len         IN  PLS_INTEGER DEFAULT NULL);

GET_RAW Function

This function 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 and returns the actual number of bytes requested by the GET_RAW len parameter.

Syntex:

UTL_FILE.GET_RAW (

   fid  IN  utl_file.file_type,

   r    OUT NOCOPY RAW,

   len  IN  PLS_INTEGER DEFAULT NULL);

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.

Syntex:

UTL_FILE.IS_OPEN (

   file  IN FILE_TYPE)

  RETURN BOOLEAN;

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.

Syntex:

UTL_FILE.NEW_LINE (

   file     IN FILE_TYPE,

   lines    IN NATURAL := 1);

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.

Syntex:

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.

Syntex:

UTL_FILE.PUT_LINE (

   file      IN FILE_TYPE,

   buffer    IN VARCHAR2,

   autoflush IN BOOLEAN DEFAULT FALSE);

For a real life example of UTL_FILE package, please Click Here.