I shall list the public constructs/components of UTL_FILE package specification categorized according to their area of operation.
FILE_TYPE is a record type, which is a public construct of UTL_FILE package. It is used to declare a UTL_FILE file handler, which is mandatory in majority of UTL_FILE operations, starting from opening of the file till closing it.
The structure of the record is as below
Since the file handler components are private to UTL_FILE, any reference to the record type attributes would raise an exception.
UTL_FILE contains subprograms to open a file in specified mode, verify directory path and file existence, read or write data into the file, and closing it. I shall present them in order of their operational utility.
Subprogram Name: IS_OPEN Subprogram Type: Function Syntax:
Description: The function checks for a file handler, whether it points to an open file or not.
The OPEN utility lists the UTL_FILE methods which are used to open a disk file.
Subprogram Name: FOPEN Subprogram Type: Function Syntax:
Description: The function opens a file from the directory in the specified mode and assigns the file handler with the appropriate file id. The directory specification is case sensitive and must be accessible to the user. If the file open process succeeds, it returns a file handler, else raises appropriate exception. The file with the specified filename must exist at the directory location for read/write operations. Note that the file handler retuned during OPEN operation has to be used in all subsequent file operations.
The modes can be any of r for Read Text, w for Write Text, a for Append Text, rb for Read byte mode, wb for Write byte mode or ab for Append byte mode. The modes a and ab have the ability of creating a new file in write mode, if it doesnt exists at the time of specification.
The last parameter max_linesize is the maximum length of the characters in a line of the file. The value lies in the range of 1to 32767. By default, its value is 1024.
Subprogram Name: FOPEN_NCHAR Subprogram Type: Function Syntax:
Description: The function works similar to the FOPEN function but different in the character set mode. It opens the file in national character set mode for all operations, which is different from the database character set. The parameter legends remain the same as that of FOPEN.
The section lists the UTL_FILE subprograms which deal with read operations on the file.
Subprogram Name: GET_LINE Subprogram Type: Procedure Syntax:
Description: The procedure returns a text contained by the file. The file, in read mode, allows a line of text to be read and assigned to the out buffer parameter. LEN specifies the characters of line to be read in each attempt. If it is null, then the procedure reads the line till the line is terminated, provided the length is less than the max_linesize.
Subprogram Name: GET_LINE _NCHAR Subprogram Type: Procedure Syntax:
Description: Its role is same as that of GET_LINE. But the difference is with the character sets. It follows Unicode set instead of database character set. The file must be opened in national character set mode and must be encoded in UTF8 character set.
Subprogram Name: GET_RAW Subprogram Type: Procedure Syntax:
Description: This procedure too works similar to GET_LINE. But it reads RAW data from the file, while later reads a text file in regular format.
The section lists the UTL_FILE subprograms which are used for write operations on a file.
Subprogram Name: NEW_LINE Subprogram Type: Procedure Syntax:
Description: The procedure writes a new line character into the file, under Write operation.
Oracle 11g Tutorial