Chapter 55. UTL

Chapter 55. UTL_FILE

This chapter briefly introduces the UTL_FILE package, and describes how to use the procedures and functions of the package.

55.1. Overview

UTL_FILE provides functions and procedures to access files administered in the operating system. The file path can be specified with directory names.

Defined types and exceptions in the UTL_FILE package are as follows:

Type

The FILE_TYPE type is used as the file ID.

TYPE FILE_TYPE IS RECORD
(
    id       BINARY_INTEGER, 
    datatype BINARY_INTEGER
);

Exception

Exception	Description
INVALID_PATH	The file location is invalid.
INVALID_MODE	An invalid mode was used.
INVALID_FILEHANDLE	The file handle is invalid.
INVALID_OPERATION	The file operation failed.
READ_ERROR	An error occurred during the read operation.
WRITE_ERROR	An error occurred during the write operation.
INTERNAL_ERROR	A unexpected error occurred.
FILE_OPEN	The operation failed because the file is already open.
INVALID_MAXLINESIZE	The max_linesize value for the FOPEN function is invalid. The value of max_linesize must be between 1 and 32,767 bytes.
INVALID_FILENAME	The filename is invalid.
ACCESS_DENIED	Permission to access the file has not been granted.
INVALID_OFFSET	Cases when an invalid offset is passed: - When both the relative offset and absolute offset are NULL. - When the absolute offset is negative. - When the offset extends beyond the end of the file.
DELETE_FAILED	The DELETE operation failed.
RENAME_FAILED	The RENAME operation failed.

55.2. Procedures

This section describes the procedures provided by the UTL_FILE package, in alphabetical order.

55.2.1. FCLOSE

Closes an open file.

Details about the FCLOSE procedure are as follows:

Prototype

PROCEDURE FCLOSE
(
   file IN OUT FILE_TYPE
);

Parameter

Parameter Description
file File handle.
Exception
- WRITE_ERROR
- INVALID_FILEHANDLE

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/HOME/TIBERO/PATH';

DECLARE
  fname   VARCHAR2(1024);
BEGIN
    file := UTL_FILE.FOPEN('USER_PATH', 'MYFILE.DAT', 'r');
    UTL_FILE.FCLOSE(file);
END;
/

55.2.2. FCLOSE_ALL

Closes all open files in the session.

Details about the FCLOSE_ALL procedure are as follows:

Prototype
```
PROCEDURE FCLOSE_ALL;
```
Exception
- WRITE_ERROR

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/HOME/TIBERO/PATH';

DECLARE
    outfile   UTL_FILE.FILE_TYPE;
    appfile   UTL_FILE.FILE_TYPE;
    path       VARCHAR(10);
BEGIN
    path := 'USER_PATH';

    outfile := UTL_FILE.FOPEN(path, 'output_file.txt', 'w');
    appfile := UTL_FILE.FOPEN(path, 'append_file.txt', 'w');

    UTL_FILE.FCLOSE_ALL;
END;
/

55.2.3. FCOPY

Copies a contiguous portion of a file to a new file.

Details about the FCOPY procedure are as follows:

Prototype

PROCEDURE FCOPY
( 
    location   IN VARCHAR2,
    filename   IN VARCHAR2, 
    dest_dir   IN VARCHAR2,
    dest_file  IN VARCHAR2,
    start_line IN BINARY_INTEGER DEFAULT 1, 
    end_line   IN BINARY_INTEGER DEFAULT NULL
);

Parameter

Parameter	Description
location	Directory path of the source file.
filename	Source file name.
dest_dir	Destination file directory path.
dest_file	Destination file name.
start_line	Starting line number of the lines to copy. Default value: 1, indicates the first line of the file.
end_line	Ending line number of the lines to copy. Default value: NULL, indicates the last line of the file.

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/HOME/TIBERO/PATH';
         
BEGIN
    UTL_FILE.FCOPY('USER_PATH', 'MYFILE.DAT', 'USER_PATH', 'MTFILE.BAK');
END;
/

55.2.4. FFLUSH

Writes data to a file. Writes unwritten data from the buffer. The data must end with an EOL character.

Details about the FFLUSH procedure are as follows:

Prototype

PROCEDURE FFLUSH
(
    file IN FILE_TYPE
);

Parameter

Parameter Description
file File handle.
Exception
- INVALID_FILEHANDLE
- INVALID_OPERATION
- WRITE_ERROR

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';
DECLARE
  fhandle  UTL_FILE.FILE_TYPE;
  buffer   VARCHAR2(32767);
  path     VARCHAR2(1024);
  fname    VARCHAR2(1024);
BEGIN
  path     := 'USER_PATH';
  fname    :=  'MYFILE.TXT';
  fhandle  := UTL_FILE.FOPEN(path, fname, 'w');
  buffer   := 'This is the message for output file';
  for i in 1..10 loop
    UTL_FILE.PUT(fhandle, buffer);
  end loop;
  UTL_FILE.NEW_LINE(fhandle);
  UTL_FILE.FFLUSH(fhandle);
  UTL_FILE.FCLOSE(fhandle);
END;
/

55.2.5. FGETATTR

Returns the file attributes from disk.

Details about the FGETATTR procedure are as follows:

Prototype

PROCEDURE FGETATTR 
(
    location IN VARCHAR2,
    filename IN VARCHAR2,
    fexists  OUT BOOLEAN,
    file_length OUT NUMBER,
    blocksize OUT BINARY_INTEGER
);

Parameter

Parameter Description
location File path.
filename File name.
fexists Indicates whether or not the file exists.
file_length File length, in bytes.
blocksize File system block size, in bytes.

Parameter	Description
location	File path.
filename	File name.
fexists	Indicates whether or not the file exists.
file_length	File length, in bytes.
blocksize	File system block size, in bytes.

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
  info_exists  BOOLEAN;
  info_flen    NUMBER;
  info_bsz     BINARY_INTEGER;
  etc_path     VARCHAR2(1024);
  fname        VARCHAR2(1024);
BEGIN
  etc_path := 'USER_PATH';
  fname    := 'hostname';
  UTL_FILE.FGETATTR(etc_path, fname, info_exists, info_flen, info_bsz);

  if info_exists = TRUE then
    DBMS_OUTPUT.PUT_LINE('file name         :' || fname);
    DBMS_OUTPUT.PUT_LINE('file size (bytes) :' || TO_CHAR(info_flen));
    DBMS_OUTPUT.PUT_LINE('block size(bytes) :' || TO_CHAR(info_bsz));
  end if;
END;
/

55.2.6. FREMOVE

Deletes a file. If the user does not have sufficient permissions to delete the file, a DELETE_FAILED exception occurs.

Details about the FREMOVE procedure are as follows:

Prototype

PROCEDURE FREMOVE
(
    location IN VARCHAR2,
    filename IN VARCHAR2
);

Parameter

Parameter Description
location File path.
filename File name.
Exception
- DELETE_FAILED

Parameter	Description
location	File path.
filename	File name.

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

BEGIN
    UTL_FILE.FREMOVE('USER_PATH', 'MYFILE.BAK');
END;
/

55.2.7. FRENAME

Renames a file name. This is similar to the mv shell command.

Details about the FRENAME procedure are as follows:

Prototype

PROCEDURE FRENAME 
(
    location   IN VARCHAR2,
    filename   IN VARCHAR2, 
    dest_dir   IN VARCHAR2, 
    dest_file  IN VARVCHAR2,
    overwrite  IN BOOLEAN DEFAULT FALSE
);

Parameter

Parameter	Description
location	Directory path of the source file.
filename	Source file name.
dest_dir	Destination file directory path.
dest_file	New file name.
overwrite	Option to replace the original file if the new file name already exists.

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';
BEGIN
    UTL_FILE.FRENAME('USER_PATH', 'MTFILE.BAK', 'USER_PATH', 'MYFILE.DAT');
END;
/

55.2.8. FSEEK

Moves the position of the file pointer.

Details about the FSEEK procedure are as follows:

Prototype

PROCEDURE FSEEK 
(
    fid IN UTL_FILE.FILE_TYPE,
    absolute_offset IN PLS_INTEGER DEFAULT NULL,
    relative_offset IN PLS_INTEGER DEFAULT NULL
);

Parameter

Parameter	Description
fid	File ID of the FILE_TYPE type in the UTL_FILE package.
absolute_offset	Absolute offset value from the beginning of the file. The default value is NULL.
relative_offset	Relative offset value. If this is positive, the file pointer moves forward, and if it is negative, the file pointer moves backward. If an absolute offset is specified, the relative offset is ignored. The default value is NULL.

Description

fid

File ID of the FILE_TYPE type in the UTL_FILE package.

absolute_offset

Absolute offset value from the beginning of the file.

The default value is NULL.

relative_offset

Relative offset value. If this is positive, the file pointer moves forward, and if it is negative, the file pointer moves backward. If an absolute offset is specified, the relative offset is ignored.

The default value is NULL.

Exception
- INVALID_OFFSET

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
  infile          UTL_FILE.FILE_TYPE;
  absolute_offset PLS_INTEGER:= 10;
  scan_first      RAW(32767);
  scan_second     RAW(32767);
  len CONSTANT    PLS_INTEGER := 32767;
BEGIN
  infile := UTL_FILE.FOPEN('USER_PATH','codd.bcnf', 'r');

  UTL_FILE.FSEEK(infile, absolute_offset, -5);
  UTL_FILE.GET_RAW(infile, scan_first, 1);
  UTL_FILE.FSEEK(infile, 5);
  UTL_FILE.GET_RAW(infile, scan_second, 1);

  if  scan_first != scan_second then
    DBMS_OUTPUT.PUT_LINE('ralative_offset ignored!');
  end if;

  UTL_FILE.FCLOSE(infile);
END;
/

55.2.9. GET_LINE

Reads text data from a file until it reaches an EOL character or the end of the file. The text size should not be larger than the max_linesize, which is set through the FOPEN function.

Details about the GET_LINE procedure are as follows:

Prototype

PROCEDURE GET_LINE 
(
    file   IN  FILE_TYPE,
    buffer OUT VARCHAR2,
    len    IN  PLS_INTEGER DEFAULT NULL
);

Parameter

Parameter	Description
file	File handle.
buffer	Text buffer with data read from the file.
len	Number of bytes to read from the file. Default value: NULL (If set to NULL, max_linesize is used)

Exception
- INVALID_FILEHANDLE
- INVALID_OPERATION

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE 
    infile       UTL_FILE.FILE_TYPE;
    length       CONSTANT PLS_INTEGER := 1024;
    read_buffer  VARCHAR2(1024) := NULL;
BEGIN
    infile := UTL_FILE.FOPEN('USER_PATH','hosts', 'r');
    loop
        UTL_FILE.GET_LINE(infile, read_buffer, length);
        DBMS_OUTPUT.PUT_LINE(read_buffer);
        if read_buffer is NULL then
             DBMS_OUTPUT.PUT_LINE(read_buffer);
        else
            exit;
        end if;
    end loop;

    UTL_FILE.FCLOSE(infile);
END;
/

55.2.10. GET_RAW

Reads RAW text from a file. This procedure ignores EOL characters, and returns the number of bytes read.

Details about the GET_RAW procedure are as follows:

Prototype

PROCEDURE GET_RAW
(
    fid  IN  UTL_FILE.FILE_TYPE,
    r    OUT NOCOPY RAW,
    len  IN  PLS_INTEGER DEFAULT NULL
);

Parameter

Parameter	Description
fid	File identifier.
buffer	Text buffer with data read from the file.
len	Number of bytes to read from the file. Default value: NULL (If set to NULL, max_linesize is used)

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE 
  infile       UTL_FILE.FILE_TYPE;
  length       CONSTANT PLS_INTEGER := 32;
  read_buffer  RAW(32767);
BEGIN
  infile := UTL_FILE.FOPEN('USER_PATH','hosts', 'r');
  UTL_FILE.GET_RAW(infile, read_buffer, length);
  UTL_FILE.FCLOSE(infile);
END;
/

55.2.11. NEW_LINE

Writes one or more EOL characters to a file. Different EOL characters can be written depending on the platform.

Details about the NEW_LINE procedure are as follows:

Prototype

PROCEDURE NEW_LINE
(
    file  IN FILE_TYPE,
    lines IN NATURAL DEFAULT 1 
);

Parameter

Parameter Description
file File handle.
lines Number of EOL characters to write.
Exception
- INVALID_FILEHANDLE
- INVALID_OPERATION
- WRITE_ERROR

55.2.12. PUT

Writes text data to a file.

Details about the PUT procedure are as follows:

Prototype

PROCEDURE PUT
(
    file IN FILE_TYPE,
    buffer IN VARCHAR2
);

Parameter

Description

file

File handle.

buffer

Text buffer with lines to write to the file.

If the file has not been opened in 'w' (write) or 'a' (append) mode, an INVALID_OPERATION exception occurs.

Exception
- INVALID_FILEHANDLE
- INVALID_OPERATION
- WRITE_ERROR

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';
DECLARE
  outfile  UTL_FILE.FILE_TYPE;
  buf      VARCHAR(1024) := NULL;
BEGIN
  outfile := UTL_FILE.FOPEN('USER_PATH', 'MYFILE.DAT', 'w');

  for i in 1..10 loop
    buf := buf || '0123456789';
  end loop;

  UTL_FILE.PUT( outfile, buf );
  UTL_FILE.FCLOSE(outfile);
END;
/

55.2.13. PUTF

Formatted PUT procedure. Like formatted strings in fprintf in C, formatted strings of PUTF include '%s' and escape characters. '\n' can be used as an escape character, and '%s' is replaced by the corresponding argument string that follows.

Example:

arg1 = 'string1'; 
arg2 = 'string2'; 
arg3 = 'string3';

If the formatted strings for the three arguments are

utl_file.putf( ofile, 'This is example of formatted string : %s %s %s \n',
               arg1, arg2, arg3);

then the following message will be output:

This is example of formatted string : string1 string2 string3

Details about the PUTF procedure are as follows:

Prototype

PROCUDURE PUTF 
(
    file   IN FILE_TYPE,
    format IN VARCHAR2,
    arg1   IN VARCHAR2 DEFAULT NULL,
    arg2   IN VARCHAR2 DEFAULT NULL,
    arg3   IN VARCHAR2 DEFAULT NULL,
    arg4   IN VARCHAR2 DEFAULT NULL,
    arg5   IN VARCHAR2 DEFAULT NULL
);

Parameter

Parameter	Description
file	File handle.
format	Formatted string that contains '%s' and escape characters (\n).
arg1-5	String value that will be substituted for '%s'. The default value is NULL.

Exception
- INVALID_FILEHANDLE
- INVALID_OPERATION
- WRITE_ERROR

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
    fname    VARCHAR2(2048);
    path     VARCHAR2(2048);
    outfile  UTL_FILE.FILE_TYPE;
BEGIN
    fname := 'MYFILE.DAT';
    path  := 'USER_PATH';
    outfile := UTL_FILE.FOPEN(path, fnamet /home/posung/path/MYFILE.DAT!cat
               /home/posung/path/MYFILE.DAT, 'w');
    UTL_FILE.PUTF( outfile, '%s %s formated file output example\n', path, fname);
    UTL_FILE.FCLOSE(outfile);
END;
/

55.2.14. PUT_RAW

Writes RAW data values to a file.

Details about the PUT_RAW procedure are as follows:

Prototype

PROCEDURE PUT_RAW
(
    fid       IN UTL_FILE.FILE_TYPE, 
    r         IN RAW, 
    autoflush IN BOOLEAN DEFAULT FALSE 
);

Parameter

Parameter	Description
fid	File identifier.
r	Data to write to the file.
autoflush	Option to empty the output buffer after writing the data. TRUE: empty the buffer. FALSE: do not empty the buffer. The default value is FALSE.

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
    outfile  UTL_FILE.FILE_TYPE;
    buf RAW(8) := HEXTORAW('6161616100616100');
BEGIN
    outfile := UTL_FILE.FOPEN('USER_PATH', 'MYFILE.DAT', 'wb');

    UTL_FILE.PUT_RAW(outfile, buf);

    UTL_FILE.FCLOSE(outfile);
END;
/

55.2.15. PUT_LINE

Writes a line to a file. The line ends with a platform-specific EOL character.

Details about the PUT_LINE procedure are as follows:

Prototype

PROCEDURE PUT_LINE
(
    file      IN FILE_TYPE,
    buffer    IN VARCHAR2,
    autoflush IN BOOLEAN DEFAULT FALSE
);

Parameter

Parameter	Description
fid	File handle.
r	Line to write to the file.
autoflush	Option to empty the output buffer after writing the data. TRUE: empty the buffer. FALSE: do not empty the buffer. The default value is FALSE.

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
    outfile  UTL_FILE.FILE_TYPE;
    out_buf  VARCHAR2(1024);
BEGIN
    outfile := UTL_FILE.FOPEN('USER_PATH', 'MYFILE.DAT', 'w');

    for i in 1..10 loop
        UTL_FILE.PUT_LINE(outfile,  i || ' put_line is complete', TRUE);
    end loop;

    UTL_FILE.FCLOSE(outfile);
END;
/

55.3. Functions

This section describes the functions provided by the UTL_FILE package, in alphabetical order.

55.3.1. FGETPOS

Returns the relative position of the file pointer in a file, in bytes.

Details about the FGETPOS function are as follows:

Prototype

FUNCTION FGETPOS
(
    fid  IN FILE_TYPE
) 
RETURN PLS_INTEGER;

Parameter

Parameter Description
fid File handle.

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
  file   UTL_FILE.FILE_TYPE;
  fpos   PLS_INTEGER := 0;
  path   VARCHAR2(1024);
  fname  VARCHAR2(1024);
BEGIN
    file := UTL_FILE.FOPEN('USER_PATH', 'MYFILE.DAT', 'r');
    UTL_FILE.FSEEK(file, 10, 1);
    fpos := UTL_FILE.FGETPOS(file);
    DBMS_OUTPUT.PUT_LINE('Offest is ' || TO_CHAR(fpos, 'S9999999999'));

    UTL_FILE.FCLOSE(file);
END;
/

55.3.2. FOPEN

Opens a file. Up to 50 files can be opened at the same time.

Details about the FOPEN function are as follows:

Prototype

FUNCTION FOPEN
(
    location     IN VARCHAR2,
    filename     IN VARCHAR2,
    open_mode    IN VARCHAR2, 
    max_linesize IN BINARY_INTEGER DEFAULT 1024
) 
RETURN FILE_TYPE;

Parameter

Parameter	Description
location	Directory path to file(s).
filename	File name.
open_mode	File mode. r: read text. w: write text. a: append text. rb: read bytes. wb: write bytes. ab: append bytes. If no file exists, the file is created in wb mode.
max_linesize	Maximum number of bytes per line, including newline characters. A number from 1 to 32,767 can be used. The default value is 1,024 bytes.

Exception
- INVALID_PATH
- INVALID_MODE
- INVALID_OPERATION
- INVALID_MAXLINESIZE

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
    infile    UTL_FILE.FILE_TYPE;
    outfile   UTL_FILE.FILE_TYPE;
    appfile   UTL_FILE.FILE_TYPE;
    max_lsz   CONSTANT BINARY_INTEGER := 4096;
    buffer    VARCHAR2(1024);
    data1     VARCHAR2(1024);
    data2     VARCHAR2(1024);
    path      VARCHAR2(1024);
BEGIN
    path := 'USER_PATH';

    outfile := UTL_FILE.FOPEN(path, 'output_file.txt', 'w',  max_lsz);
    appfile := UTL_FILE.FOPEN(path, 'append_file.txt', 'w',  max_lsz);

    data1 := '0123456789';
    data2 := 'abcdefghij';
    for  i in 1..10 loop
        UTL_FILE.PUT_LINE(outfile, data1);
        UTL_FILE.PUT_LINE(outfile, data2);
    end loop;

    UTL_FILE.FCLOSE(outfile);
    UTL_FILE.FCLOSE(appfile);

    infile := UTL_FILE.FOPEN(path, 'output_file.txt', 'r',  max_lsz);
    UTL_FILE.GET_LINE(infile, buffer);
    DBMS_OUTPUT.PUT_LINE(buffer);
    UTL_FILE.FCLOSE(infile);
    appfile := UTL_FILE.FOPEN( path, 'append_file.txt', 'a',  max_lsz );
    UTL_FILE.PUT_LINE(appfile, 'appended');
    UTL_FILE.FCLOSE(appfile);
END;
/

55.3.3. IS_OPEN

Tests the file handle to see whether or not the file is open. Returns TRUE if the file is open and FALSE otherwise.

Details about the IS_OPEN function are as follows:

Prototype

PROCEDURE IS_OPEN
(
    file IN  FILE_TYPE
) 
RETURN BOOLEAN;

Parameter

Parameter Description
file File handle.

Example

CREATE OR REPLACE DIRECTORY USER_PATH AS '/home/user/path';

DECLARE
    openfile    UTL_FILE.FILE_TYPE;
    open_flag   BOOLEAN;
    status      VARCHAR(10);
    fname       VARCHAR(10);
BEGIN
    fname := 'MYFILE.DAT';
    openfile := UTL_FILE.FOPEN('USER_PATH', fname, 'r');
    if UTL_FILE.IS_OPEN(openfile) then
        status := 'opened';
    else
        status := 'not opened';
    end if;

    DBMS_OUTPUT.PUT_LINE(fname || ' file status : ' || status);
    UTL_FILE.FCLOSE(openfile);
    dbms_output.put_line(fname || 'is closed');
    if utl_file.IS_OPEN(openfile) then
        status := 'opened';
    else
        status := 'not opened';
    end if;
    DBMS_OUTPUT.PUT_LINE(fname || ' file status : ' || status);
END;
/

Prev		Next
Chapter 54. UTL_ENCODE	Home	Chapter 56. UTL_HTTP