Oracle UTL_FILE
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Read and write operating system files in text or binary mode. UTL_FILE provides a restricted version of operating system stream file I/O.
AUTHID CURRENT_USER
Data Types -- file handle used in the block declaration section
TYPE file_type IS RECORD (
id BINARY_INTEGER, datatype BINARY_INTEGER, byte_mode BOOLEAN);
Dependencies
DBMS_AW_EXP DBMS_STREAMS_AUTH KUPW$WORKER
DBMS_CMP_INT DBMS_STREAMS_MT LTADM
DBMS_CUBE DBMS_STREAMS_SM LTUTIL
DBMS_DATAPUMP_UTL DBMS_SUMREF_UTIL MGMT_DB_LL_METRICS
DBMS_FILE_GROUP DBMS_SWRF_INTERNAL ORDX_FILE_SOURCE
DBMS_FILE_GROUP_UTL_INVOK DBMS_WORKLOAD_REPLAY ORD_DATASOURCE_PKG
DBMS_ISCHED DBMS_WRR_INTERNAL ORD_DICOM_ADMIN
DBMS_LOGMNR_INTERNAL DBMS_XDB_ADMIN PLITBLM
DBMS_MACADM DBMS_XMLPARSER PRVT_ADVISOR
DBMS_PREUP DBMS_XSLPROCESSOR SDO_NET
DBMS_QOPATCH DBMS_XSTREAM_ADM_INTERNAL SDO_NET_PARTITION
DBMS_REGISTRY_SYS DBMS_XSTREAM_AUTH_IVK SDO_ROUTER_PARTITION
DBMS_REPCAT_MIGRATION JVMFCB SDO_WFS_LOCK
DBMS_SCHEDULER KUPF$FILE UTL_FIL_LIB
DBMS_SQLTCB_INTERNAL KUPM$MCP  
Documented Yes
Exceptions
Error Code Reason
ORA-29280 Specified path does not exist or is not visible to Oracle
ORA-29281 The open_mode parameter in FOPEN is invalid
ORA-29282 File handle does not exist
ORA-29283 File could not be opened or operated on as requested
ORA-29284 Unable to read file
ORA-29285 Unable to write to file
ORA-29286 Unhandled internal error in the UTL_FILE package
ORA-29287 The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767
ORA-29288 A file with the specified name does not exist in the path
ORA-29289 Access to the file has been denied by the operating system
ORA-29290 The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; it should be greater than 0 and less than the total number of bytes in the file
ORA-29291 Unable to delete file
ORA-29292 Unable to rename file
ORA-29298 A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE
FILE_OPEN File is already open
First Available 7.3.4
Intialization Parameters utl_file_dir=<directory_path_and_name>
-- it is recommended that these not be used for but, instead, create a directory object
utl_file_dir=c:\oraload
utl_file_dir=c:\temp
utl_file_dir=*
Open Modes
Syntax Description
A Append Text
AB Append Byte Mode
R Read Text
RB Read Byte Mode
W Write Text
WB Write Byte Mode
Security Model Owned by SYS with EXECUTE granted to PUBLIC. The Library recommends that this grant be revoked immediately upon installation due to the security risk it poses.

O/S permissions are those of the user 'Oracle' ... not the schema owner or connected user.
Source {ORACLE_HOME}/rdbms/admin/utlfile.sql
Subprograms
 
Demo Setup
O/S Directory Creation -- windows
mkdir c:\oraload

-- linux
su -
mkdir /stage
chown -R oracle:dba /stage
Oracle Directory Creation -- windows
conn sys@pdbdev as sysdba
CREATE OR REPLACE DIRECTORY oraload AS 'c:\oraload\';
GRANT READ,WRITE ON DIRECTORY oraload TO UWCLASS;

-- linux
conn sys@pdbdev as sysdba
CREATE OR REPLACE DIRECTORY oraload AS '/stage';
GRANT READ,WRITE ON DIRECTORY oraload TO UWCLASS;
File To Create: test.txt Foote,Richard
Forbrich,Hans
Kyte, Tom
Morgan, Daniel
Nanda,Arup
 
FCLOSE
Close named file utl_file.fclose(file IN OUT file_type);
See FOPEN Demo Below
 
FCLOSE_ALL
Close all open files utl_file.fclose_all;
set serveroutput on

DECLARE
 vInHandle  utl_file.file_type;
 vOutHandle utl_file.file_type;
BEGIN
  vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');
  vOutHandle := utl_file.fopen('ORALOAD', 'out.txt', 'W');
 
  IF utl_file.is_open(vInHandle) THEN
    utl_file.fclose_all;
    dbms_output.put_line('Closed All');
  END IF;
END fopen;
/
 
FCOPY
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);
BEGIN
  utl_file.fcopy('ORALOAD', 'test.txt', 'ORALOAD', 'dump.txt');
END;
/
 
FFLUSH
Physically writes pending data to the file identified by the file handle utl_file.fflush(file IN file_type);
See WRITE Demo Below
 
FGETATTR
Reads and returns the attributes of a disk file utl_file.fgetattr(
location    IN  VARCHAR2,
filename    IN  VARCHAR2,
fexists     OUT BOOLEAN,
file_length OUT NUMBER,
block_size  OUT BINARY_INTEGER);
set serveroutput on

DECLARE
 ex    BOOLEAN;
 flen  NUMBER;
 bsize NUMBER;
BEGIN
  utl_file.fgetattr('ORALOAD', 'test.txt', ex, flen, bsize);

  IF ex THEN
    dbms_output.put_line('File Exists');
  ELSE
    dbms_output.put_line('File Does Not Exist');
  END IF;
  dbms_output.put_line('File Length: ' || TO_CHAR(flen));
  dbms_output.put_line('Block Size: ' || TO_CHAR(bsize));
END fgetattr;
/
 
FGETPOS
Returns the current relative offset position within a file, in bytes utl_file.fgetpos(file IN file_type) RETURN BINARY_INTEGER;
See READ_WRITE Demo Below
 
FOPEN
Open a file for read operations utl_file.fopen(
file_location IN VARCHAR2,
file_name     IN VARCHAR2,
open_mode     IN VARCHAR2,
max_linesize  IN BINARY_INTEGER DEFAULT NULL)
RETURN file_type;
DECLARE
 vInHandle utl_file.file_type;
 vNewLine  VARCHAR2(250);
BEGIN
  vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');
  LOOP
    BEGIN
      utl_file.get_line(vInHandle, vNewLine);
      dbms_output.put_line(vNewLine);
    EXCEPTION
      WHEN OTHERS THEN
        EXIT;
    END;
  END LOOP;
  utl_file.fclose(vInHandle);
END fopen;
/
Open A File For Write Operations <file_handle> := utl_file.fopen(<file_location, file_name, 'W')
Open A File For Read Byte Operations <file_handle> := utl_file.fopen(<file_location, file_name, 'RB')
Open A File For Write Byte Operations <file_handle> := utl_file.fopen(<file_location, file_name, 'WB')
Open A File For Append Operations <file_handle> := utl_file.fopen(<file_location, file_name, 'A')
Open A File For Append Byte Operations <file_handle> := utl_file.fopen(<file_location, file_name, 'AB')
 
FOPEN_NCHAR
Open a file for multibyte characters

Note: since NCHAR contains mutibyte character, it is recommended that the max_linesize be less than 6400
utl_file.fremove(
location     IN VARCHAR2,
filename     IN VARCHAR2,
open_mode    IN VARCHAR2,
max_linesize IN BINARY_INTEGER DEFAULT NULL)
RETURN file_type;
See FOPEN Demo Above
 
FREMOVE
Delete a file in the file system utl_file.fremove(location IN VARCHAR2, filename IN VARCHAR2);
-- dump.txt is created in the FCOPY demo
BEGIN
  utl_file.fremove('ORALOAD', 'dump.txt');
END fremove;
/
 
FRENAME
Rename a file system file utl_file.frename (
src_location  IN VARCHAR2,
src_filename  IN VARCHAR2,
dest_location IN VARCHAR2,
dest_filename IN VARCHAR2,
overwrite     IN BOOLEAN DEFAULT FALSE);
BEGIN
  utl_file.frename('ORALOAD','test.txt','ORALOAD','x.txt',TRUE);
END frename;
/
 
FSEEK
Adjusts the file pointer forward or backward within the file by the number of bytes specified utl_file.fseek(
file            IN OUT file_type,
absolute_offset IN     BINARY_INTEGER DEFAULT NULL,
relative_offset IN     BINARY_INTEGER DEFAULT NULL);
See Read-Write Demo Below
 
GET_LINE
Read a Line from a file utl_file.get_line(
file   IN  FILE_TYPE,
buffer OUT VARCHAR2,
len    IN  BINARY_INTEGER DEFAULT NULL);
See Read Demos Below
 
GETLINE_NCHAR
Read a line from a file containing multi-byte characters utl_file.getline_nchar(
file   IN  FILE_TYPE,
buffer OUT NVARCHAR2,
len    IN  BINARY_INTEGER DEFAULT NULL);
See Read Demos Below
 
GET_RAW
Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read utl_file.get_raw(
file   IN  file_type,
buffer OUT NOCOPY RAW,
len    IN  BINARY_INTEGER DEFAULT NULL);
See UTL_MAIL Demo Linked At Page Bottom
 
IS_OPEN
Returns True If A File Handle Is Open: Otherwise False utl_file.is_open(file IN FILE_TYPE) RETURN BOOLEAN;
See FCLOSE_ALL Demo Above
 
NEW_LINE
Writes one or more operating system-specific line terminators to a file utl_file.new_line(file IN FILE_TYPE, lines IN NATURAL := 1);
See Read Demo Below
 
PUT
Write a string to a file utl_file.put(file IN FILE_TYPE, buffer IN VARCHAR2);
See WRITE Demo Below
 
PUTF
A PUT procedure with formatting utl_file.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);
See WRITE Demo Below
 
PUTF_NCHAR
Writes a Unicode string to a file utl_file.putf_nchar(
file   IN file_type,
format IN NVARCHAR2,
arg1   IN NVARCHAR2 DEFAULT NULL,
arg2   IN NVARCHAR2 DEFAULT NULL,
arg3   IN NVARCHAR2 DEFAULT NULL,
arg4   IN NVARCHAR2 DEFAULT NULL,
arg5   IN NVARCHAR2 DEFAULT NULL);
TBD
 
PUT_LINE
Writes a line to a file.  Appends an operating system-specific line terminator utl_file.put_line(
file      IN FILE_TYPE,
buffer    IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE);
See READ-WRITE Demo Below
 
PUT_LINE_NCHAR
Writes a Unicode line to a file utl_file.put_line_nchar(file IN file_type, buffer IN NVARCHAR2);
TBD
 
PUT_NCHAR
Writes a Unicode string to a file utl_file.put_nchar(file IN file_type, buffer IN NVARCHAR2);
TBD
 
PUT_RAW
Accepts as input a RAW data value and writes the value to the output buffer utl_file.put_raw(
file      IN file_type,
buffer    IN RAW,
autoflush IN BOOLEAN DEFAULT FALSE);
See EXTRACT_BLOB Demo Below
 
UTL_FILE Demos
Read Demo conn uwclass/uwclass@pdbdev

create table test (
fld1 VARCHAR2(20),
fld2 VARCHAR2(20));

CREATE OR REPLACE PROCEDURE read_demo(file_name VARCHAR2) IS
 vSFile   utl_file.file_type;
 vNewLine VARCHAR2(200);
BEGIN
  vSFile := utl_file.fopen('ORALOAD', file_name,'r');

  IF utl_file.is_open(vSFile) THEN
    LOOP
      BEGIN
        utl_file.get_line(vSFile, vNewLine);

        IF vNewLine IS NULL THEN
          EXIT;
        END IF;

        INSERT INTO test
        (fld1, fld2)
        VALUES
        (vNewLine, file_name);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
      END;
    END LOOP;
    COMMIT;
  END IF;
  utl_file.fclose(vSFile);
  utl_file.frename('ORALOAD', 'test.txt', 'ORALOAD', 'x.txt', TRUE);
EXCEPTION
  WHEN utl_file.invalid_mode THEN
    RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
  WHEN utl_file.invalid_path THEN
    RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
  WHEN utl_file.invalid_filehandle THEN
    RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
  WHEN utl_file.invalid_operation THEN
    RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
  WHEN utl_file.read_error THEN
    RAISE_APPLICATION_ERROR (-20055, 'Read Error');
  WHEN utl_file.internal_error THEN
    RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
  WHEN utl_file.charsetmismatch THEN
    RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
    But Later I/O Inconsistent');
  WHEN utl_file.file_open THEN
    RAISE_APPLICATION_ERROR (-20059, 'File Already Opened');
  WHEN utl_file.invalid_maxlinesize THEN
    RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
  WHEN utl_file.invalid_filename THEN
    RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
  WHEN utl_file.access_denied THEN
    RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By');
  WHEN utl_file.invalid_offset THEN
    RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
  WHEN others THEN
    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END read_demo;
/
Read-Write Demo conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE rw_demo IS
 InFile   utl_file.file_type;
 OutFile  utl_file.file_type;
 vNewLine VARCHAR2(4000);
 i        PLS_INTEGER;
 j        PLS_INTEGER := 0;
 SeekFlag BOOLEAN := TRUE;
BEGIN
  -- open a file to read
  InFile := utl_file.fopen('ORALOAD', 'in.txt','r');
  -- open a file to write
  OutFile := utl_file.fopen('ORALOAD', 'out.txt', 'w');

  -- if the file to read was successfully opened
  IF utl_file.is_open(InFile) THEN
    -- loop through each line in the file
    LOOP
      BEGIN
        utl_file.get_line(InFile, vNewLine);

        i := utl_file.fgetpos(InFile);
        dbms_output.put_line(TO_CHAR(i));

        utl_file.put_line(OutFile, vNewLine, FALSE);
        utl_file.fflush(OutFile);

        IF SeekFlag = TRUE THEN
          utl_file.fseek(InFile, NULL, -30);
          SeekFlag := FALSE;
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
      END;
    END LOOP;
    COMMIT;
  END IF;
  utl_file.fclose(InFile);
  utl_file.fclose(OutFile);
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
END rw_demo;
/
Write Demo

This demo writes out a Korn Shell script to run SQL*Loader
conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE create_cmd_file AS
 CURSOR sll_cur IS
 SELECT loadname, loadfilename, loadfiledate
 FROM sqlldrlog
 WHERE run_status = 'B'
 ORDER BY sequenceno;

 sll_rec   sll_cur%ROWTYPE;

 DirLoc    VARCHAR2(30) := 'ORALOAD';
 LFileName sqlldrlog.loadfilename%TYPE;
 LFileDate sqlldrlog.loadfiledate%TYPE;
 ctl_file  VARCHAR2(500);
 dat_file  VARCHAR2(500);
 log_file  VARCHAR2(500);
 bad_file  VARCHAR2(500);
 Emsg      VARCHAR2(90) := 'Load CREATE_CMD_FILE Failed with ERROR ';
 vSubject := 'SQL Loader Failure Notification';
 DayFile  utl_file.file_type;
 LogFile  utl_file.file_type;
BEGIN
  DayFile := utl_file.fopen(DirLoc, 'execsqlldr.ksh','W');
  LogFile := utl_file.fopen(DirLoc, 'log_list.dat','W');

  OPEN sll_cur;
  LOOP
    FETCH sll_cur INTO sll_rec;
    EXIT WHEN sll_cur%NOTFOUND;

    ctl_file := '/data/cload/ctl/'|| LOWER(sll_rec.loadname) || '.ctl \';

    dat_file := '/data/cload/data/' || sll_rec.loadfilename || ' \';

    log_file := '/data/cload/log/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.log \';

    bad_file := '/data/cload/bad/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.bad';

    utl_file.putf(dayfile, 'sqlldr userid=%s\ncontrol=%s\ndata=%s\nlog=%s\nbad=%s\n', '/ \',
ctl_file, dat_file, log_file, bad_file);

    log_file := '/data/cload/log/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.log';

    utl_file.putf(logfile,'%s\n',log_file);
  END LOOP;
  utl_file.fclose(DayFile);
  utl_file.fclose(LogFile);
EXCEPTION
  WHEN utl_file.invalid_mode THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20051, Invalid Option';
    sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage);
  WHEN utl_file.invalid_path THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20052, Invalid Path';
    sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage);
  WHEN utl_file.invalid_filehandle THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20053, Invalid Filehandle';
    sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage);
  WHEN utl_file.invalid_operation THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20054, Invalid Operation';
    sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage);
  WHEN utl_file.read_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20055, Read Error';
    sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage);
  WHEN utl_file.write_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20056, Write Error';
    sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage);
  WHEN utl_file.internal_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20057, Internal Error';
  WHEN OTHERS THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || vErrMsg;
    sp_SendEmail (0, 'dba@mlib.org', vSubject, vMessage);
END create_cmd_file;
/
Extract BLOB Demo conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE blob2file(
pdname VARCHAR2, psname VARCHAR2, pfname VARCHAR2) IS

 vblob   BLOB;
 vstart  NUMBER := 1;
 bytelen NUMBER := 32000;
 len     NUMBER;
 my_vr   RAW(32000);
 x       NUMBER;
 l_output utl_file.file_type;
BEGIN
  -- define output directory
  l_output := utl_file.fopen('ORALOAD', pfname, 'WB', 32760);

  -- get length of blob
  SELECT dbms_lob.getlength(iblob)
  INTO len
  FROM pdm
  WHERE dname = pdname
  AND sname = psname
  AND fname = pfname;

  -- save blob length
  x := len;

  -- select blob into variable
  SELECT iblob
  INTO vblob
  FROM pdm
  WHERE dname = pdname
  AND sname = psname
  AND fname = pfname;

  -- if small enough for a single write
  IF len < 32760 THEN
    utl_file.put_raw(l_output,vblob);
    utl_file.fflush(l_output);
  ELSE -- write in pieces
    vstart := 1;
    WHILE vstart < len
    LOOP
      dbms_lob.read(vblob,bytelen,vstart,my_vr);

      utl_file.put_raw(l_output,my_vr);
      utl_file.fflush(l_output)

      -- set the start position for the next cut
      vstart := vstart + bytelen;

      -- set the end position if less than 32000 bytes
      x := x - bytelen;
      IF x < 32000 THEN
        bytelen := x;
      END IF;
    END LOOP;
  END IF;
  utl_file.fclose(l_output);
END blob2file;
/

Related Topics
DBMS_LOB
Directory Objects
Packages
UTL_MAIL

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved