Understanding UTL_FILE in Oracle PL/SQL

On more than fewer occasions, while developing PL/SQL programs, we are required to read / write data from text files that reside on our operating systems.  UTL_FILE package lets us to read / write files from our PL/SQL programs.

In this post, I shall try to explain some of the procedures that are primarily used during this process and a sample code is provided at the end of the post. The objective of the sample code would be “read” the data from a csv file and then to “write” data into another file placed on the server.

Some of the primary procedures used to read and write using UTL_FILE are:

UTL_FILE.fgetattr: This procedure is used to check if the file exists or not in the specified directory location. It also returns the attributes (properties) of a file.

The input and output parameters  are:

  1. location: Directory location of the source file, DIRECTORY_NAME from ALL_DIRECTORIESview (case sensitive).
  2. filename: Name of the file to be processed.
  3. fexists: The procedure returns TRUE value if the file exists in the directory.
  4. file_length: Length of the file in bytes.
  5. block_size: File system block size in bytes.

UTL_FILE.fopen: This function opens the file that needs to be processed (read or write).

The input parameters are:

  1. location: Directory location of file. This string is a directory object name and must be specified in upper case.
  2. filename: File name, including extension (file type), without directory path. If a directory path is given as a part of the filename, it is ignored by FOPEN.
  3. open_mode: The mode in which the file has to be opened in. The mostly used attributes are: r  -> read and w -> write.
  • UTL_FILE.get_line: Once the file is opened, the get_line procedure reads the text from the open file places the text in the output buffer parameter. If the len parameter is null – then the oracle provides the max_linesize value to that parameter.

The parameters are:

  1. file: Active file handle returned by an FOPEN call.
  2. buffer: Data buffer to receive the line read from the file.
  • UTL_FILE.put_line: The put_line procedure writes the text string stored in the buffer parameter to the open file identified by file handle.

The parameters are:

  1. file: The file that is being processed and is called using the active file handle.
  2. buffer: Text buffer that contains the text lines that need to be written to the file.
  • UTL_FILE.fclose: This fclose procedure closes the file that is opened earlier. This is a mandatory statement during the UTL_File process. Not closing the file would keep the file open and make it unable to move or delete the file at a later stage.

 

The Input read file – Test_UTL_Read_File.csv has the following content:

In order to explain UTL_FILE, below is the sample code that uses some of the procedures explained above. The objective of this code is to:

  • Check to see if the file exists.
  • Open the file.
  • Read the data from the file line by line.
  • Write the data into another file on the same server.
  • Close the file.

After the UTL_File completes the Write process into a file Test_UTL_Write_File.csv, then the final output is as below:

 

Leave a Reply

  • (will not be published)