Application of PL/SQL--UTL_FILE package

In PLSQL, The UTL_FILE package provides the interaction between input and output of text files. That is to say, we can use this package to achieve file read input or

In PL/SQL, The UTL_FILE package provides the interaction between input and output of text files. That is to say, we can use this package to achieve file read input or

In PL/SQL, The UTL_FILE package provides the interaction between input and output of text files. That is to say, we can use this package to read or write files from the operating system level to the operating system file. This package can also load data from other systems to the database. Such as loading web server logs, user login database logs, and even Oracle log files. This article describes the UTL_FILE function and demonstrates and understands the usage of related process functions in this package through examples.

1. UTL_FILE Introduction
A. Read and Write Functions Based on the operating system level
B. This mode is based on the server-side text file access mode and does not support binary files.
C. You can set the utl_file_dir parameter to set multiple paths for pl/SQL to access operating system files.
D. All users can read and write the directories set by the utl_file_dir parameter. Therefore, security issues should be considered.
E. You can also leave the utl_file_dir parameter empty, and access the OS file by creating a directory and granting the directory permission (recommended)

2. Procedures and functions in the UTL_FILE package
A. The file_type defined in UTL_FILE is the record type. As shown below, its members are private and cannot be directly referenced or changed.

TYPE file_type is record (
Id BINARY_INTEGER,
Datatype BINARY_INTEGER,
Byte_mode BOOLEAN );

B. Descriptions of related process functions in UTL_FILE
FCLOSE Procedure Closes a file
FCLOSE_ALL Procedure Closes all open file handles
FCOPY Procedure Copies a contiguous portion of a file to a newly created file
FFLUSH Procedure Physically writes all pending output to a file
FGETATTR Procedure Reads and returns the attributes of a disk file
FGETPOS Function Returns the current relative offset position within a file, in bytes
FOPEN Function Opens a file for input or output
FOPEN_NCHAR Function Opens a file in Unicode for input or output
FREMOVE Procedure Deletes a disk file, assuming that you have sufficient privileges
FRENAME Procedure Renames an existing file to a new name, similar to the UNIX mv function
FSEEK Procedure Adjusts the file pointer forward or backward within the file by the number of bytes specified
GET_LINE Procedure Reads text from an open file
GET_LINE_NCHAR Procedure Reads text in Unicode from an open file
GET_RAW Procedure Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
IS_OPEN Function Determines if a file handle refers to an open file
NEW_LINE Procedure Writes one or more operating system-specific line terminators to a file
PUT Procedure Writes a string to a file
PUT_LINE Procedure Writes a line to a file, and so appends an operating system-specific line terminator
PUT_LINE_NCHAR Procedure Writes a Unicode line to a file
PUT_NCHAR Procedure Writes a Unicode string to a file
PUTF Procedure a put procedure with formatting
PUTF_NCHAR Procedure A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting
PUT_RAW Procedure Accepts as input a RAW data value and writes the value to the output buffer

3. Demonstrate ULT_FILE usage

A. Main steps for using UTL_FILE (using directory)
-- Create a directory for storing OS files first
Scott @ USBO> ho mkdir-p/u03/database/usbo/db_utl_dir

-- Add directory at the database level
Scott @ USBO> create directory db_utl_dir as '/u03/database/usbo/db_utl_dir ';

-- Grant Permissions
Scott @ USBO> grant read, write on directory db_utl_dir to public;

B. Write Data from SQL query to data files
DECLARE
Vsfile UTL_FILE.file_type; ---> defines the type used to receive file handles
V_cnt PLS_INTEGER: = 0;
BEGIN
Vsfile: =
UTL_FILE.fopen ('db _ UTL_DIR ', ---> Use fopen to open a file and define the file path, file name, read/write mode, and the maximum length of each line of characters. The default value is 1024.
'Emp.txt ',
'W ',
200 );

FOR I IN (SELECT t. ename | ',' | t. job AS msg ---> A for loop is used to read the scott. emp table.
FROM scott. emp t WHERE t. sal> 2000)
LOOP
UTL_FILE.put_line (vsfile, I. msg); ---> write the content of the for Loop query to the file using put_line
V_cnt: = v_cnt + 1; ---> counter, used to count the number of records written
End loop;

UTL_FILE.fflush (vsfile );
UTL_FILE.fclose (vsfile );
DBMS_OUTPUT.put_line (v_cnt | 'rows unloaded ');
END;
/

6 rows unloaded

PL/SQL procedure successfully completed.