| General Information |
|
Note: O/S permissions are those of the user 'Oracle' ... not the schema owner or connected user
|
| Source |
{ORACLE_HOME}/rdbms/admin/utlfile.sql |
| First Availability |
7.3.4 |
| 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 |
SELECT name FROM dba_dependencies WHERE referenced_name = 'UTL_FILE'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'UTL_FILE'); |
Exceptions |
| Exception Name |
Error Code |
Reason |
| access_denied |
29289 |
Access to the file has been denied by the operating system |
| charsetmismatch |
29298 |
A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE |
| delete_failed |
29291 |
Unable to delete file |
| file_open |
? |
File is already open |
| internal_error |
29286 |
Unhandled internal error in the UTL_FILE package |
| invalid_filehandle |
29282 |
File handle does not exist |
| invalid_filename |
29288 |
A file with the specified name does not exist in the path |
| invalid_maxlinesize |
29287 |
The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767 |
| invalid_mode |
29281 |
The open_mode parameter in FOPEN is invalid |
| invalid_offset |
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 |
| invalid_operation |
29283 |
File could not be opened or operated on as requested |
| invalid_path |
29280 |
Specified path does not exist or is not visible to Oracle |
| read_error |
29284 |
Unable to read file |
| rename_failed |
29292 |
Unable to rename file |
| write_error |
29285 |
Unable to write to file |
|
| init.ora Parameters
(deprecated) |
utl_file_dir=<directory_path_and_name> |
utl_file_dir=c:\oraload
utl_file_dir=c: emp
utl_file_dir=* |
Open Modes |
| A |
Append Text |
| AB |
Append Byte Mode |
| R |
Read Text |
| RB |
Read Byte Mode |
| W |
Write Text |
| WB |
Write Byte Mode |
|
| Security Model |
Execute is granted to PUBLIC which is a security risk. It is recommended that this grant be revoked
immediately following installation.
|
conn / as sysdba
REVOKE execute ON utl_file FROM public;
|
| |
| Demo Setup |
| O/S Directory Creation |
mkdir c:\oraload |
| Oracle Directory Creation |
CREATE DIRECTORY oraload AS 'c:\oraload\';
GRANT READ,WRITE ON DIRECTORY oraload TO UWCLASS; |
| File To Create: test.txt |
Daniel,Morgan
Jack,Cline |
| |
| FCLOSE |
| Close named file |
utl_file.fclose(file IN OUT file_type); |
| see FOPEN demo |
| |
| FCLOSE_ALL |
Close all 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(
location IN VARCHAR2,
filename 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(<location, filename, 'W') |
| |
| 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.fopen_nchar(
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER DEFAULT NULL)
RETURN file_type; |
| TBD |
| |
| FREMOVE |
Delete An Operating System File |
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 An Operating 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 |
| |
| GETLINE |
Read a Line from a file |
utl_file.getline(
file IN FILE_TYPE,
buffer OUT VARCHAR2,
len IN BINARY_INTEGER DEFAULT NULL); |
| See Read demos |
| |
| 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 |
| |
| 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 |
| |
| 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 |
| |
| 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 |
| |
| PUT |
| Writes 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 |
| |
| 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_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 |
| |
| PUT_LINE_NCHAR |
| Writes a Unicode line to a file |
utl_file.put_line_nchar(file IN file_type, buffer IN NVARCHAR2); |
| TBD |
| |
| 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 |
| |
| UTL_FILE Demos |
Read Demo |
create table test (
fld1 VARCHAR2(20),
fld2 VARCHAR2(20));
CREATE OR REPLACE PROCEDURE read_demo(filename VARCHAR2) IS
vSFile utl_file.file_type;
vNewLine VARCHAR2(200);
BEGIN
vSFile := utl_file.fopen('ORALOAD', filename,'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, filename);
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
|
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 |
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
control=%s
data=%s
log=%s
bad=%s
', '/ \',
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
',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, '[email protected]', vSubject, vMessage);
WHEN utl_file.invalid_path THEN
vErrMsg := SQLERRM;
vMessage := Emsg || '-20052, Invalid Path';
sp_SendEmail (0, '[email protected]', vSubject, vMessage);
WHEN utl_file.invalid_filehandle THEN
vErrMsg := SQLERRM;
vMessage := Emsg || '-20053, Invalid Filehandle';
sp_SendEmail (0, '[email protected]', vSubject, vMessage);
WHEN utl_file.invalid_operation THEN
vErrMsg := SQLERRM;
vMessage := Emsg || '-20054, Invalid Operation';
sp_SendEmail (0, '[email protected]', vSubject, vMessage);
WHEN utl_file.read_error THEN
vErrMsg := SQLERRM;
vMessage := Emsg || '-20055, Read Error';
sp_SendEmail (0, '[email protected]', vSubject, vMessage);
WHEN utl_file.write_error THEN
vErrMsg := SQLERRM;
vMessage := Emsg || '-20056, Write Error';
sp_SendEmail (0, '[email protected]', 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, '[email protected]', vSubject, vMessage);
END create_cmd_file;
/
|
Extract BLOB Demo |
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;
/
|
