UTL_FILE package of ORACLE

The UTL_FILE package of ORACLE UTL_FIle is used to read and write files in the operating system, provided that the Directory object and authorization are created first. Then you can use the types, procedures, and functions provided in the UTL_FILE package to read, write, and operate the files in the directory. Example of creating a Directory at www.2cto.com: SQL> CREATE DIRECTORY CZW AS 'd: \ '; Directory createdSQL> GRANT READ, WRITE ON DIRECTORY CZW TO SCOTT; grant succeeded 1. FILE_TYPE: The record type defined in the UTL_FILE package. Its members are private and cannot be directly referenced. This type is defined as follows: TYPE file_type is record (id BINARY_INTEGER, datatype BINARY_INTEGER); www.2cto.com 2, FOPEN this function IS used to open a file. You can use this function to open up to 50 files. Syntax: UTL_FILE.FOPEN (location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type; after the function is successfully executed, a file handle is returned, which can be used to access the file. If the execution fails, exceptions or errors are triggered. Note: When specifying the file location, you must use the DIRECTORY object and its name must be in uppercase. Example: declare handle UTL_FILE.FILE_TYPE; begin handle: = UTL_FILE.fopen ('czw', 'dywt. TXT ', 'R', 1000); DBMS_OUTPUT.PUT_LINE ('d: \ DYWT. TXT has been opened '); END; 3. FOPEN_NCHAR this function is used to open a file in UNICODE mode. When you use this function to open a file, the Read/Write File uses UNICODE to replace the database character set. Syntax: UTL_FILE.FOPEN_NCHAR (location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN file_type; 4. IS_OPEN: UTL_FILE.IS_OPEN (file IN FILE_TYPE) return boolean; as shown above, file is used to specify the file handle. If the file has been opened, TRUE is returned; otherwise, FALSE is returned. The function example is as follows: declare handle UTL_FILE.FILE_TYPE; beginif not UTL_FILE.is_open (HANDLE) then handle: = UTL_FILE.fopen ('czw', 'dywt. TXT ', 'R', 1000); end if; DBMS_OUTPUT.PUT_LINE ('d: \ DYWT. TXT has been opened '); END; 5. FCLOSE is used to close opened files. Syntax: UTL_FILE.FCLOSE (file in out FILE_TYPE ); 6. FCLOSE_ALL is used to close all open files. Syntax: UTL_FILE.FCLOSE_ALL; 7. GET_LINE this process is used to read the row content from an opened file. The row content is read to the output buffer. Syntax: UTL_FILE.GET_LINE (file IN FILE_TYPE, buffer OUT VARCHAR2, linesize in number, len IN PLS_INTEGER default null); As shown above, buffer is used to store read information; linesize is used to specify the maximum number of bytes to read, and len is used to specify the actual length of the read. Example of using this process: declare handle UTL_FILE.FILE_TYPE; BUFFER VARCHAR2 (100); beginif not UTL_FILE.is_open (HANDLE) then handle: = UTL_FILE.fopen ('czw', 'dywt. TXT ', 'R', 1000); end if; UTL_FILE.GET_LINE (HANDLE, BUFFER, 100); DBMS_OUTPUT.PUT_LINE (BUFFER); UTL_FILE.fclose (HANDLE); END; 8. The GET_LINE_NCHAR process is used in the same way as above, except that the row content of opened files is read in UNICODE mode and read to the output buffer. 9. GET_RAW this process is used to read RAW strings from the file and adjust the file pointer to the read location. Syntax: UTL_FILE.GET_RAW (fid IN UTL_FILE.TYPE, r out nocopy raw, len IN PLS_INTEGER default null); As shown above: fid is used to specify the file handle, and r is used to obtain read information, as shown IN the following example: declare handle UTL_FILE.FILE_TYPE; BUFFER VARCHAR2 (2000); beginif not UTL_FILE.is_open (HANDLE) then handle: = UTL_FILE.fopen ('czw', 'dywt. TXT ', 'R', 1000); end if; UTL_FILE.GET_RAW (HANDLE, BUFFER, 1000); DBMS_OUTPUT.PUT_LINE (BUFFER); UTL_FILE.fclose (HANDLE); END; 10. PUT this process is used to set the buffer Write data to a file. When the PUT process is used, the file must be opened in write mode. After the buffer is written, if you want to end the row, you can use the NEW_LINE process. Syntax: UTL_FILE.PUT (file IN FILE_TYPE, buffer IN VARCHAR2); example of using this process: declare handle UTL_FILE.FILE_TYPE; BUFFER VARCHAR2 (2000); beginif not UTL_FILE.IS_OPEN (HANDLE) then handle: = UTL_FILE.FOPEN ('czw', 'new. TXT ', 'w', 1000); end if; BUFFER: =' & content1'; UTL_FILE.PUT (HANDLE, BUFFER); UTL_FILE.NEW_LINE (HANDLE); BUFFER: = '& content2'; UTL_FILE.PUT (HANDLE, BUFFER); UTL_FILE.NEW_LINE (HANDLE); UTL_FILE.FCLOSE (HANDLE ); END; 11. PUT_NCHAR is used to write the buffer content to the file in UNICODE mode. 12. PUT_RAW: This process is used to write data in the RAW buffer to a file. Syntax: UTL_FILE.PUT_RAW (fid IN UTL_FILE.FILE_TYPE, r in raw, autoflush in boolean default false). fid is used to specify the file handle, and r is used to specify the buffer for storing RAW data, autoflush is used to specify whether to automatically refresh the buffer data. example of this process: declare handle UTL_FILE.FILE_TYPE; BUFFER VARCHAR2 (2000); beginif not UTL_FILE.IS_OPEN (HANDLE) then handle: = UTL_FILE.FOPEN ('czw', 'new. TXT ', 'w', 1000); end if; BUFFER: =' & content1'; UTL_FILE.PUT_RAW (HANDLE, BUFFER); UTL_FILE.NEW_LINE (HANDLE ); UTL_FILE.FCLOSE (HANDLE); END; 13. NEW_LINE this process is used to add a line terminator to the file. Syntax: UTL_FILE.NEW_LINE (file IN FILE_TYPE, lines in natural: = 1 ); the lines below are used to specify the number of line terminologies added to the file. 14. PUT_LINE this process is used to write the text buffer content to the file. When this process is used to Append content to a file, the row Terminator is automatically appended to the end of the file. Declare handle UTL_FILE.FILE_TYPE; BUFFER VARCHAR2 (2000); beginif not UTL_FILE.is_open (HANDLE) then handle: = UTL_FILE.fopen ('czw', 'dywt. TXT ', 'w', 1000); end if; BUFFER: =' & content'; UTL_FILE.put_line (HANDLE, BUFFER); UTL_FILE.fclose (HANDLE); END; note: the data is deleted by adding a row Terminator after the newly written row. 15. PUT_LINE_NCHAR is the same as above. This process is used to write text buffer content to files in UNICODE mode. When you use this process to write content, the row Terminator is automatically appended to the end. After testing, the content in the original file is cleared. 16. PUTF this process is used to write text content to a file in a specific format, where % s identifies the string, and the format \ n indicates the line terminator. Syntax: UTL_FILE.PUTF (file IN FILE_TYPE, format IN VARCHAR2, [arg1 IN VARCHAR2 default null ,... arg5 IN VARCHAR2 default null]); the preceding format is used to specify format characters (up to five % s are supported), arg1... arg5 is used to specify the string corresponding to the format character. Example of using this process: declare handle UTL_FILE.FILE_TYPE; BUFFER VARCHAR2 (2000); beginif not UTL_FILE.is_open (HANDLE) then handle: = UTL_FILE.fopen ('czw', 'dywt. TXT ', 'w', 1000); end if; UTL_FILE.PUTF (HANDLE,' % s \ n % s \ n', '& line1 ', '& line2',' & line3 '); UTL_FILE.FCLOSE (HANDLE); END; 17. PUTF_NCHAR is the same as above, this process is used to write text content to a file in UNICODE format. 18. FFLUSH this process is used to forcibly write data to a file. Normally, when writing data to the file, the data will be temporarily put into the cache. The FFLUSH process is used to forcibly write data to a file. Syntax: UTL_FILE.FFLUSH (file IN FILE_TYPE): 19. FSEEK this process is used to move the file pointer to a specific location. When this process is used to move a file pointer, you can specify the absolute position of the file pointer or the relative position of the file pointer. Syntax: UTL_FILE.FSEEK (fid IN utl_file.FILE_TYPE, absolute_offset IN PL_INTEGER defalut null, relative_offset IN PL_INTEGER defalut null); As shown above, absolute_offset is used to specify the absolute position of the file, and relative_offset is used to specify the relative position of the file (IN bytes ), example of using this process: declare handle UTL_FILE.FILE_TYPE; BUFFER VARCHAR2 (2000); beginif not UTL_FILE.is_open (HANDLE) then handle: = UTL_FILE.fopen ('czw', 'dywt. TXT', 'R', 1000); end if; DBMS_OUTPUT.PUT_LINE ('starting position '|' => '| UTL_FILE.FGETPOS (HANDLE); UTL_FILE.fseek (HANDLE, 5 ); DBMS_OUTPUT.PUT_LINE ('current location '|' => '| UTL_FILE.FGETPOS (HANDLE); UTL_FILE.FCLOSE (HANDLE); END; 20. FREMOVE is used to delete disk files. Syntax: UTL_FILE.FREMOVE (location IN VARCHAR2, filename IN VARCHAR2: SQL> EXEC UTL_FILE.FREMOVE ('czw', 'new. TXT '); PL/SQL procedure successfully completed 21, FCOPY this process is used to copy all or part of the source file to the target file. When this process is used, if no start row or end row is set, all contents of the file will be copied. Syntax: 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 defalut null ); the above location is used to specify the DIRECTORY object where the source file is located, filename is used to specify the specific file name, dest_dir is used to specify the directory object of the file to be copied, and dest_file is used to specify the name of the target file, start_line is used to specify the start line number and end_line is used to specify the end line number. The following is an example of UTL_FILE.FCOPY: SQL> exec UTL_FILE.FCOPY ('czw', 'dywt. TXT ', 'czw', 'czw. TXT '); PL/SQL procedure successfully completed 22, FRENAME this process is used to modify the name of an existing file, it acts on the same UNIX MV command, when modifying the file name, you can specify the overwrite parameter to overwrite existing files. Syntax: UTL_FILE.FRENAME (location IN VARCHAR2, filename IN VARCHAR2, dest_dir IN VARCHAR2, dest_file IN VARCHAR2, overwrite in boolean default false); above, overwrite is used to specify whether to overwrite existing files (false does not overwrite, true can overwrite) Example: SQL> exec UTL_FILE.FRENAME ('czw', 'czw. TXT ', 'czw', 'hanjb. TXT ') PL/SQL procedure successfully completed www.2cto.com example: Use the UTL_FILE package to export the data of a table in the database in a certain format: create a DIRECTORY named "CZW_DIR" declare handle UTL_FILE.FILE_TYPE; begin handle: = UTL_FILE.FOPEN ('czw _ dir', 'dept. TXT ', 'w', 1000); for I IN (SELECT T. DEPTNO | ',' | T. DNAME | ',' | T. loc as msg from scott. dept t) LOOP UTL_FILE.PUT_LINE (HANDLE, I. MSG); end loop; UTL_FILE.FFLUSH (HANDLE); UTL_FILE.FCLOSE (HANDLE); END;