Oracle UTL_FILE

Oracle UTL_FILE

Version 10.2

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

Related Topics Directories DBMS_LOB

Contact Us ? Legal Notices and Terms of Use ?Privacy Statement