Oracle's Utl_file Package usage detailed

The Utl_file package can be used to read and write text files on the operating system, Utl_file provides file access functions on the client (form, etc.) and on the server side. Create a test directory: Create a new command window; directory: (Log in to the database as System user) SQL code Collection code SQL> Create or Replace directory Cux_log_dir as '/home/appltest/debug '; Directory created assign permissions. SQL code Collection Code SQL> Grant Read, write on directory Cux_log_dir to Public; Grant succeeded checks whether the directory successfully created the SQL Code collection code Select* from All_directories dir WHERE dir. Directory_name = ' Cux_log_dir '; Ps: View All_directories holds the directory objects that we can access. If you want to delete the directory, you also need the system user to log into the database, execute the following command: Drop directory cux_log_dir; Procedures and Functions: fopen Description: Open a file, basically before we read and write to the file, we need to execute this function to open the file first. Syntax: Utl_file. FOPEN (Varchar2,filename in Varchar2,open_mode in Varchar2,max_linesize in Binary_integer DEFAULT1024) RETURN file_type; parameters: Location slightly.            Filename slightly. Open_mode indicates the mode in which the file is opened. There are several: r – Read-only (text) W – Write only (this document) a – append (text) rb– read-only (bytes) wb– write-only (bytes) ab– append (bytes) (Note: When using patterns: A or AB, if the file     Does not exist, this file is created in write mode) max_linesize Specifies the maximum number of characters per line that the file text will hold. Return value: fopen Returns a pointer to the file that our program will use Fclose function: Close an open file. Syntax: Utl_file. FCLOSE (file in Out file_type); Parameters:1. file->invokes a file pointer in the activity returned by fopen or Fopen_nvchar. Note: When Fclose executes, if there is buffer data not written to the file in time, then the program will raise an exception: Write_error. You can add parameters to the Put_Line autoflush=TRUE, or execute after each put: FFLUSH. Fclose_all function: This procedure will close all open files for this session. It is used for emergency cleanup functions, e.g. when PL/when the SQL program exits in the exception section. Syntax: Utl_file. Fclose_all; Note: Fclose_all will not modify the state of the open file, that is, after the Fclose_all, and then use Is_open to detect the file, the results are open, but the files are still not able to read or write. When the fclose executes, the relevant files are completely closed, and the test: The result is: fcopy function: This procedure copies a contiguous portion of a file or all the content to a newly created file. If the arguments start_line and End_line are omitted, the entire file is copied by default. This opens the source file in read mode and opens the destination file in write mode. Syntax: Utl_file. Fcopy (src_location in Varchar2,src_filename in Varchar2,dest_location in Varchar2,dest_filename in VARCHAR2,start_l ine in Binary_integer DEFAULT1, end_line in Binary_integer DEFAULT NULL); Parameter: Src_location The directory name of the source file.    The source of the value is the directory_name of the view all_directories; src_filename The source file that will be copied dest_location the directory name of the target file that was created.    Dest_filename the target file created from the source file.    Start_line the start line number of the content to be copied, which defaults to 1, which means copying from the first row. End_line the terminating line number of the content to be copied, default null, which represents the end of the file. Before testing the program: Test code: After testing the program: and l001-There are only two lines of content in the Copy.log file: Fflush Description: Fflush forces the buffered data to be written to the file. Because the data that is normally written to the file is both in the buffered storage location. Fflush works when it is necessary to read a file that is still open, for example, in a debugger, the debug message can be rushed to the file in a timely manner so that we can read the content immediately. Syntax: Utl_file.  FFLUSH (file in File_type); Fgetattr Description: Fgetattr reads the files on the disk and returns the properties of the file. Syntax: Utl_file. Fgetattr (Varchar2,filename in varchar2,fexists out boolean,file_length out number,block_size out BINARY_INTE   GER);      Parameter: Location here omits x characters.      filename here omits x words.      Fexists returns the property 1: Whether the file exists File_length returns the property 2: File byte length, or null if the file does not exist. The byte size of the Block_size file system block. Test: SQL code Collection code declare l_loc all_directories.directory_name%TYPE: = ' Cux_log_dir ';    L_file Utl_file.file_type;    L_file_exsits BOOLEAN;    L_file_length number;    L_block_size Binary_integer; L_buffer VARCHAR2 (1024); BEGIN utl_file.fgetattr ( location=l_loc, filename= ' L001.log ', Fexists=l_file_exsits, File_length=L_file_length, Block_size=l_block_size); IF L_file_exsits then l_file:= Utl_file.fopen (location =l_loc, filename= ' L001.log ', Open_mode= ' R '); Dbms_output.put_line (' File Exsits '); Dbms_output.put_line (' File length: ' | |l_file_length); Dbms_output.put_line (' Block Sieze: ' | |l_block_size);  END IF;    Utl_file.fclose_all;    END; Output Result: File Exsitsfile length:39802Block Sieze:4096fgetpos Description: This function returns the current offset position in a file. Syntax: Utl_file.   Fgetpos (file in file_type) RETURN Pls_integer; Note: If file is not open, an exception will be thrown. Test: SQL code Collection code declare l_loc all_directories.directory_name%TYPE: = ' Cux_log_dir ';    L_file Utl_file.file_type; L_buffer VARCHAR2 (32767); BEGIN L_file:= Utl_file.fopen (location =l_loc, filename= ' L001.log ', Open_mode= ' R '); Dbms_output.put_line (' Before get_line:current position is ' | | Utl_file.fgetpos (file =l_file)); Utl_file.get_line (File=l_file, Buffer=L_buffer); Dbms_output.put_line (' After get_line:current position ' | | Utl_file.fgetpos (file =l_file));    Utl_file.fclose_all;      END; Results: Before get_line:current position is0After get_line:current position is3  Fremove Description: This procedure deletes a file on a disk if you have sufficient permissions. Syntax: Utl_file. Fremove (VARCHAR2, filename in VARCHAR2); Frename Description: This procedure renames an existing file, similar to the UNIX command: MV Syntax: Utl_file. Frename (src_location in VARCHAR2, src_filename in VARCHAR2, dest_location in VARCHAR2, dest_filename in VARCHAR2, overwrite in BOOLEAN DEFAULT FALSE); Parameters: Introduction. Get_line Description: This procedure reads a line of text from an open file until a newline character is encountered. Syntax: Utl_file.   Get_line (file in File_type, buffer out VARCHAR2, Len in Pls_integer DEFAULT NULL); Parameter: Len reads the length of the text one time, and defaults to the Max_linesieze when Null,oracle takes fopen. Is_open Description: As the name implies. Syntax: Utl_file.is_open (FILE in File_type) RETURN BOOLEAN; Put description: Put writes the contents to the file. (every write, without line break) syntax: Utl_file. PUT (file in File_type, buffer in VARCHAR2); Put_Line Description: Put_Line writes the contents to the file. (Add a line break at the end of each write) syntax: Utl_file. Put_Line (file in File_type, buffer in VARCHAR2, AutoFlush in BOOLEAN DEFAULT FALSE); Putf Description: Writes the formatted content to the file. Like the C-language printf () syntax: Utl_file. Putf (file in File_type, format in VARCHAR2, [arg1 in VARCHAR2 default NULL, ... arg5 in VARCHAR2 default null]); Parameter: format contains the formatted character [\ n,%S] of the content.   \ n: Represents a line break. %s: With arg1~5 of the value to replace. Complete Example program: SQL code Collection Code DECLARE l_loc all_directories.directory_name%TYPE: = ' Cux_log_dir ';        L_file Utl_file.file_type;        L_file_exsits BOOLEAN;        L_file_length number;        L_block_size Binary_integer; L_buffer VARCHAR2 (32767); --data CURSOR C_hander is SELECT fu.user_name, fu.description from Fnd_user Fu wher E1 = 1and Fu.user_name like' XXX% 'ORDER by Fu.user_name; BEGIN utl_file.fgetattr ( location=l_loc, filename= ' Test.log ', Fexists=l_file_exsits, File_length=L_file_length, Block_size=l_block_size); --put IF l_file_exsits then l_file:= Utl_file.fopen (location =l_loc, filename= ' Test.log ', Open_mode= ' W '); L_buffer:= ' Begining of file ... '; Utl_file.put_line (File=l_file, Buffer=L_buffer); For L in C_hander LOOP L_buffer:= L.user_name | | Chr (9) | |NVL (l.description,' No description '); Utl_file.put_line (File=l_file, Buffer=L_buffer);        END LOOP; L_buffer:= ' End of file .... '; Utl_file.put_line (File=l_file, Buffer=L_buffer); --Flush Utl_file.fflush (file=l_file); --Get L_file:= Utl_file.fopen (location =l_loc, filename= ' Test.log ', Open_mode= ' R '); Utl_file.fgetattr ( location=l_loc, filename= ' Test.log ', Fexists=l_file_exsits, File_length=L_file_length, Block_size=l_block_size); LOOP utl_file.get_line (File=l_file, Buffer=L_buffer, Len.= 32767); Dbms_output.put_line (A=L_buffer); EXIT when Utl_file.fgetpos (file= = L_file) =l_file_length;            END LOOP;      END IF;    Utl_file.fclose_all; END;

Details: http://zhangzhongjie.iteye.com/blog/1903024

Oracle's Utl_file Package usage detailed