Use of the utl_file package in Oracle

I. Usage

The package 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.

Step 2

1. create directory objects and authorization

SQL> CREATE DIRECTORY CZW AS 'd :\';
Directory created
SQL> GRANT READ, WRITE ON DIRECTORY CZW TO SCOTT;
Grant succeeded
2. Definition
H_file utl_file.file_type
3. Use
H_file = utl_file.fopen ('SQL _ TMP', 'tmp _ rollback_tables ', 'A ');
Utl_file.put_line (h_file, "strr ");
Utl_file.fclose (h_file );
Iii. function description in the UTL_FILE package
1. FILE_TYPE
This type is 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
);
2. FOPEN
This function is used to open a file. You can use this function to open up to 50 files. The syntax is as follows:
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 already 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
This function is used to determine whether the file has been opened. The syntax is as follows:
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;
BEGIN
If 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 already opened ');
END;
 
5. FCLOSE
This process is used to close opened files. The syntax is as follows:
UTL_FILE.FCLOSE (file in out FILE_TYPE );
 
6. FCLOSE_ALL
This process is used to close all files currently open. 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 be read; len is used to specify the actual read length. An example of this process is as follows:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BUFFER VARCHAR2 (100 );
BEGIN
If 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. GET_LINE_NCHAR
The usage of this process is the same as above, except that the row content of opened files is read in UNICODE mode, and the row content is 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 the read information, for example:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BUFFER VARCHAR2 (2000 );
BEGIN
If 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 write the buffer content to the 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 );
An example of this process is as follows:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BUFFER VARCHAR2 (2000 );
BEGIN
If 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
This process is used to write the buffer content to a 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
)
Above, fid is used to specify the file handle, r is used to specify the buffer for storing RAW data, and autoflush is used to specify whether to automatically refresh the buffer data. An example of this process is as follows:
 
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BUFFER VARCHAR2 (2000 );
BEGIN
If 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. The syntax is as follows:
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 );
BEGIN
If 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 previous data will be deleted by adding a row Terminator after the newly written row.
 
15. PUT_LINE_NCHAR
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 a maximum of five % s, and arg1. .. arg5 is used to specify a string corresponding to the format. An example of this process is as follows:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BUFFER VARCHAR2 (2000 );
BEGIN
If 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
Same as above, this process is used to write text content to a file in UNICODE mode in a specific format.
 
18. FFLUSH
This process is used to forcibly write data to the 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 position. 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. The syntax is as follows:
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 (in bytes) of the file. The example of this process is as follows:
DECLARE
HANDLE UTL_FILE.FILE_TYPE;
BUFFER VARCHAR2 (2000 );
BEGIN
If not UTL_FILE.is_open (HANDLE) THEN
HANDLE: = UTL_FILE.fopen ('czw', 'dywt. TXT ', 'R', 1000 );
End if;
DBMS_OUTPUT.PUT_LINE ('start 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
This process is used to delete disk files. Syntax:
UTL_FILE.FREMOVE (location IN VARCHAR2, filename IN VARCHAR2 );
The above location is used to specify the DIRECTORY. Note that the file name must be capitalized here, And filename is used to specify the file name to be deleted. The example is as follows:
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. The MV commands acting on UNIX are identical. When modifying the file name, you can specify the overwrite parameter to overwrite the existing file. Syntax:
UTL_FILE.FRENAME (
Location IN VARCHAR2,
Filename IN VARCHAR2,
Dest_dir IN VARCHAR2,
Dest_file IN VARCHAR2,
Overwrite in boolean default false );
In the preceding example, overwrite is used to specify whether to overwrite an existing file (false is not covered, and true can be overwritten:
SQL> exec UTL_FILE.FRENAME ('czw', 'czw. TXT ', 'czw', 'hanjb. TXT ')
PL/SQL procedure successfully completed