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