Oracle UTL_FILE 用法例子

 

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 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 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 from: http://www.psoug.org/reference/utl_file.html --End--