UTL_FILE in Oracle - Writing Reading for OS text file

In Oracle PL/SQL, UTL_FILE is an Oracle supplied package which is used for file operations (read and write) in conjunction with the underlying operating system. UTL_FILE works for both server and client machine systems. A directory has to be created on the server, which points to the target file. For the files located on the server machine, the actual path can be given while creating the directory. For the files which are located on the client machines, however, the relative path is required along with the client machine name. In addition, the relative file path must be in shared mode with read and write access for the required users. A DBA must create the directory and then grant Read/Write access to the required users.

In earlier versions of Oracle, the parameter UTL_FILE_DIR was used to specify the file location path. This parameter is now deprecated and directory creation method is recommended. 

UTL_FILE subprograms are listed as below.

• FCLOSE - Closes a file. 
  
  
• FCLOSE_ALL - Closes all open file handles
  
  
• FCOPY - Copies a contiguous portion of a file to a newly created file. Takes the following parameters: 
  src_location, src_filename, dest_location, dest_filename, start_line, and end_line. 
  
  
• FFLUSH - Physically writes all pending output to a file.
  
  
• FGETATTR - Reads and returns the attributes of a disk file. Returns the following items about the file: 
  location, filename, fexists (a boolean), file_length (in bytes), and block_size.
  The location must be either an existing directory on the server AND be in the utl_file_dir parameter, or it may be a directory. 
  
  
• FGETPOS - Returns the current relative offset position within a file, in bytes as a binary_integer.
  
  
• FOPEN - Opens a file for input or output. FOPEN takes the following parameters:
  the file location, the filename, the open_mode and the max_linesize.
  
  
• FOPEN_NCHAR - Opens a file in Unicode for input or output.
  
  
• FREMOVE - Deletes a disk file, assuming that you have sufficient privileges. Takes the following parameters: 
  location and filename
  
  
• FRENAME - Renames an existing file to a new name, similar to the UNIX mv function. FRENAME takes the following parameters:
  the src_location, the src_filename, the dest_location, the dest_filename, and overwrite (a boolean). The overwrite parameter determines whether or not the file, if it already exists, will be overwritten.
  
  
• FSEEK - Adjusts the file pointer forward or backward within the file by the number of bytes specified. FSEEK takes the following parameters:
  the file, the absolute_offset (a binary_integer), and the relative_offset (a binary_integer).
  
  
• GET_LINE - Reads text from an open file. GET_LINE takes the following parameters:
  the file (record), buffer (varchar2), and len (a binary_integer).
  
  
• GET_LINE_NCHAR - Reads text in Unicode from an open file. GET_LINE_NCHAR takes the following parameters:
  the file (record), buffer (nvarchar2), and len (a binary_integer).
  
  
• GET_RAW - Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read. GET_RAW takes the following parameters:
  file (record), buffer (raw), len (a binary_integer)
  
  
• IS_OPEN - Determines if a file handle refers to an open file.
  
  
• NEW_LINE - Writes one or more operating system-specific line terminators to a file. NEW_LINE takes the following parameters:
  file (record), lines (a binary_integer).
  
  
• PUT - Writes a string to a file. PUT takes the following parameters:
  file (record), buffer (a varchar2).
  
  
• PUT_LINE - Writes a line to a file, and also appends an operating system-specific line terminator. If a line was already written, it starts the line with CR/LF. This implies that the file, when being written into, does not end with CR/LF. In Oracle 9i the maximum line length that can be written is 32K. PUT_LINE takes the following parameters:
  file (record), buffer (a varchar2).
  
  
• PUT_LINE_NCHAR - Writes a Unicode line to a file. PUT_LINE takes the following parameters:
  file (record), buffer (a nvarchar2), autoflush (a boolean).
  
  
• PUT_NCHAR - Writes a Unicode string to a file. PUT takes the following parameters:
  file (record), buffer (an nvarchar2).
  
  
• PUTF - A PUT procedure with formatting.
  
  
• PUTF_NCHAR - A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting.
  
  
• PUT_RAW - Accepts as input a RAW data value and writes the value to the output buffer.

Example:

In the below example, SYSDBA creates a directory MYDIR and grants R/W access to the user SCOTT. The user then creates a text file in the directory and writes a text into it.

--------------SYSDBA-----------------------

SQL> CREATE DIRECTORY MYDIR AS 'C:\TESTLOC';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY MYDIR TO SCOTT

Grant succeeded.

--------------SCOTT-------------------------

DECLARE

  L_HANDLER UTL_FILE.FILE_TYPE;

BEGIN

  L_HANDLER := UTL_FILE.FOPEN('MYDIR', 'SYS.txt', 'W');

  UTL_FILE.PUTF(L_HANDLER, 'UTL_FILE write mode demonstration');

  UTL_FILE.FCLOSE(L_HANDLER);

END;

PL/SQL procedure successfully completed.