Oracle UTL_FILE
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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);
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) 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 19c
What's New In 20c-21c

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