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