Oracle UTL_FILE
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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_ADVISOR DBMS_QOPATCH KUPM$MCP
DBMS_AW_EXP DBMS_REGISTRY_SYS KUPW$WORKER
DBMS_CLOBUTIL DBMS_SCHEDULER LTUTIL
DBMS_CMP_INT DBMS_SQLTCB_INTERNAL MGMT_DB_LL_METRICS
DBMS_CUBE DBMS_STATS_INTERNAL ORDX_FILE_SOURCE
DBMS_DATAPUMP_UTL DBMS_STREAMS_AUTH ORD_DATASOURCE_PKG
DBMS_DISRUPT DBMS_STREAMS_MT ORD_DICOM_ADMIN
DBMS_FILE_GROUP DBMS_STREAMS_SM PLITBLM
DBMS_FILE_GROUP_UTL_INVOK DBMS_SWRF_INTERNAL SDO_NDM_TRAFFIC
DBMS_HCS_LOG DBMS_WORKLOAD_REPLAY_I SDO_NET
DBMS_ISCHED DBMS_WRR_INTERNAL SDO_NET_PARTITION
DBMS_ISCHED_REMOTE_ACCESS DBMS_XDB_ADMIN SDO_ROUTER_PARTITION
DBMS_LOGMNR_D DBMS_XMLPARSER SDO_ROUTER_TIMEZONE
DBMS_LOGMNR_INTERNAL DBMS_XSLPROCESSOR SDO_WFS_LOCK
DBMS_METADATA DBMS_XSTREAM_AUTH_IVK SDO_WFS_PROCESS
DBMS_OPTIM_BUNDLE JVMFCB UTL_FIL_LIB
DBMS_PREUP KUPF$FILE  
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, ORACLE_OCM, ORDPLUGINS, ORDSYS,  and WMSYS.

O/S permissions are those of the user 'Oracle' ... not the schema owner or connected user.
Source {ORACLE_HOME}/rdbms/admin/utlfile.sql
Subprograms
FCLOSE FREMOVE PUT
FCLOSE_ALL FRENAME PUTF
FCOPY FSEEK PUTF_NCHAR
FFLUSH GET_LINE PUT_LINE
FGETATTR GETLINE_NCHAR PUT_LINE_NCHAR
FGETPOS GET_RAW PUT_NCHAR
FOPEN IS_OPEN PUT_RAW
FOPEN_NCHAR NEW_LINE Demos
 
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;
/
 
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);
DECLARE
 fhandle utl_file.file_type;
BEGIN
  fhandle := utl_file.fopen('ORACLE_HOME', 'sitetest.xml', 'W');

 utl_file.put_line(fhandle, 'ABCDEFGHIJKLM', TRUE);
 utl_file.new_line(fhandle, 1);
 utl_file.put_line(fhandle, 'NOPQRSTUVWXYZ', TRUE);

 utl_file.fflush(fhandle);
 utl_file.fclose(fhandle);
END;
/

-- contents of the file written
ABCDEFGHIJKLM

NOPQRSTUVWXYZ
 
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) AUTHID DEFINER 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
Built-in Functions
Built-in Packages
DBMS_LOB
Directory Objects
UTL_MAIL
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx