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 |
Dependencies | 44 objects:
SELECT DISTINCT name FROM ( 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 | utl_file_dir |
utl_file_dir=c:/oraload utl_file_dir=c:/temp 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 | |
Record Type | Handle to a file. Used in the block declaration section |
TYPE file_type IS RECORD (id BINARY_INTEGER, datatype BINARY_INTEGER, byte_mode BOOLEAN); |
|
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 |
File To Create: dump.txt | Daniel,Morgan Jack,Cline |
Demo Table | create table test ( fld1 VARCHAR2(20), fld2 VARCHAR2(20)); |
File To Load | Daniel,Morgan Jack,Cline |
|
FCLOSE |
Close A File Opened By UTL_FILE | utl_file.fclose(<file_handle>) |
see FOPEN demo |
|
FCLOSE_ALL |
Close All Files Opened By UTL_FILE | 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 ( location IN VARCHAR2, filename IN VARCHAR2, dest_dir IN VARCHAR2, dest_file IN VARCHAR2, start_line IN PLS_INTEGER DEFAULT 1, end_line IN PLS_INTEGER DEFAULT NULL); |
-- demo requires creating directory CTEMP ... see link at bottom of page
BEGIN utl_file.fcopy('ORALOAD', 'dump.txt', 'ORALOAD', 'didit.txt'); END; / |
|
FFLUSH |
Physically writes pending data to the file identified by the file handle | utl_file.fflush (<file_handle>); |
See Write demo |
|
FGETATTR |
Reads and returns the attributes of a disk file | utl_file.fgetattr( location IN VARCHAR2, filename IN VARCHAR2, exists OUT BOOLEAN, file_length OUT NUMBER, blocksize OUT NUMBER); |
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(fileid IN file_type) RETURN PLS_INTEGER; |
See Read-Write demo |
|
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>) RETURN <file_type_package_data_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') |
|
FOPEN_NCHAR |
Open a file to read or write a text file in Unicode instead of in the database charset |
|
FREMOVE |
Delete An Operating System File | utl_file.fremove (location IN VARCHAR2, filename IN VARCHAR2); |
BEGIN utl_file.fremove('ORALOAD', 'dump.txt'); END fremove; / |
|
FRENAME |
Rename An Operating System File | utl_file.frename ( location IN VARCHAR2, filename IN VARCHAR2, dest_dir IN VARCHAR2, dest_file 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 ( fid IN utl_file.file_type, absolute_offset IN PL_INTEGER DEFAULT NULL, relative_offset IN PLS_INTEGER DEFAULT NULL); |
See Read-Write demo |
|
GETLINE |
Read a Line from a file | utl_file.getline ( file IN FILE_TYPE, buffer OUT VARCHAR2, linesize IN NUMBER, len IN PLS_INTEGER DEFAULT NULL); |
See Read demos |
|
GETLINE_NCHAR |
Same as GETLINE except can be used to read Unicode rather than the database's character set |
|
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 ( fid IN utl_file.file_type, r OUT NOCOPY RAW, len IN PLS_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 |
|
PUTF |
A PUT procedure with formatting | utl_file.putf( file IN FILE_TYPE, format IN VARCHAR2, [arg1 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 |
|
PUT_NCHAR |
Writes a Unicode string to a file |
|
PUT_RAW |
Accepts as input a RAW data value and writes the value to the output buffer | utl_file.PUT_RAW ( fid IN utl_file.file_type, r IN RAW, autoflush IN BOOLEAN DEFAULT FALSE); |
See extract_blob Demo |
|
PUT_LINE_NCHAR |
Writes a Unicode line to a file |
|
PUTF_NCHAR |
Writes a Unicode string to a file |
|
UTL_FILE Demos |
Read Demo | CREATE OR REPLACE PROCEDURE read_demo (file_name VARCHAR2) IS vSFile utl_file.file_type; vNewLine VARCHAR2(200); BEGIN vSFile := utl_file.fopen('ORALOAD', 'test.txt','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 | 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 InFile := utl_file.fopen('CTEMP', 'in.txt','r'); OutFile := utl_file.fopen('CTEMP', 'out.txt', 'w');
IF utl_file.is_open(InFile) THEN 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 | PROCEDURE create_cmd_file IS
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(100) := 'DailyLoad 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@psoug.org', vSubject, vMessage); WHEN utl_file.invalid_path THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20052, Invalid Path'; sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage); WHEN utl_file.invalid_filehandle THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20053, Invalid Filehandle'; sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage); WHEN utl_file.invalid_operation THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20054, Invalid Operation'; sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage); WHEN utl_file.read_error THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20055, Read Error'; sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage); WHEN utl_file.write_error THEN vErrMsg := SQLERRM; vMessage := Emsg || '-20056, Write Error'; sp_SendEmail (0, 'dba@psoug.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@psoug.org', 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('CTEMP', 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; / |