Using PL/SQL UTL_FILE to Read and Write Files

Working with files directly from the database can be an essential part of enterprise applications. Whether you need to generate reports, export data, or import information into Oracle tables, the UTL_FILE package in PL/SQL provides a reliable way to handle file input and output operations. In this article, you will learn how to use UTL_FILE to read and write files, explore its key procedures, see practical examples, and understand best practices for handling file operations safely and efficiently.

What is UTL_FILE in PL/SQL?

UTL_FILE is a built-in Oracle package that allows PL/SQL programs to interact with operating system files stored on the server. It supports operations like reading, writing, appending, and checking file status.

Before using UTL_FILE, you need to ensure that your database administrator has granted you access to specific file system directories via Oracle Directory Objects. Unlike earlier versions where UTL_FILE_DIR initialization parameter was used, modern Oracle databases (9i and above) recommend using directory objects for better security and control.

Key Features of UTL_FILE

UTL_FILE offers a wide range of functionalities:

• File Access: Open, read, write, append, and close files.
• Modes of Operation: Supports read (R), write (W), and append (A) modes.
• Line Handling: Read lines from a file or write lines to a file.
• Error Handling: Raises exceptions like INVALID_PATH, INVALID_MODE, WRITE_ERROR, etc.
• Directory Access: Securely maps database access to operating system directories.

Creating a Directory Object

Before reading or writing files, you must create a directory object in Oracle and grant the necessary permissions:

-- Create a directory object
CREATE OR REPLACE DIRECTORY my_dir AS '/u01/app/oracle/files';

-- Grant access to the user
GRANT READ, WRITE ON DIRECTORY my_dir TO hr;

Here:

• my_dir is the name of the directory object inside Oracle.
• /u01/app/oracle/files is the actual operating system path where files are stored.
• The hr user is granted read and write privileges.

Opening and Closing Files

To interact with a file, you first open it using UTL_FILE.FOPEN and close it using UTL_FILE.FCLOSE.

DECLARE
   v_file  UTL_FILE.FILE_TYPE;
BEGIN
   v_file := UTL_FILE.FOPEN('MY_DIR', 'example.txt', 'W');
   UTL_FILE.PUT_LINE(v_file, 'Hello, this is my first file!');
   UTL_FILE.FCLOSE(v_file);
END;

• FOPEN opens the file located in MY_DIR.
• 'example.txt' is the file name.
• 'W' specifies write mode.
• PUT_LINE writes a line to the file.
• FCLOSE closes the file and ensures changes are saved.

Writing to Files with UTL_FILE

You can write data to files using the following procedures:

• PUT – Writes text without adding a newline.
• PUT_LINE – Writes text and appends a newline.
• NEW_LINE – Adds a new empty line.

Example: Writing Employee Data to a File

DECLARE
   v_file  UTL_FILE.FILE_TYPE;
BEGIN
   v_file := UTL_FILE.FOPEN('MY_DIR', 'employees.txt', 'W');
   
   UTL_FILE.PUT_LINE(v_file, 'Employee Report');
   UTL_FILE.PUT_LINE(v_file, '----------------');
   
   FOR rec IN (SELECT first_name, last_name, salary FROM employees) LOOP
      UTL_FILE.PUT_LINE(v_file, rec.first_name || ' ' || rec.last_name || ' - ' || rec.salary);
   END LOOP;
   
   UTL_FILE.FCLOSE(v_file);
END;

This script generates a simple employee report and saves it as a text file.

Reading from Files with UTL_FILE

Reading files is just as important. The key procedures are:

• GET_LINE – Reads a line of text from a file.
• IS_OPEN – Checks whether a file is currently open.

Example: Reading a File Line by Line

DECLARE
   v_file   UTL_FILE.FILE_TYPE;
   v_line   VARCHAR2(4000);
BEGIN
   v_file := UTL_FILE.FOPEN('MY_DIR', 'employees.txt', 'R');
   
   LOOP
      BEGIN
         UTL_FILE.GET_LINE(v_file, v_line);
         DBMS_OUTPUT.PUT_LINE(v_line);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            EXIT; -- End of file
      END;
   END LOOP;
   
   UTL_FILE.FCLOSE(v_file);
END;

This block reads the contents of employees.txt line by line and prints them using DBMS_OUTPUT.

Appending to an Existing File

You can also append data to an existing file without overwriting its contents:

DECLARE
   v_file  UTL_FILE.FILE_TYPE;
BEGIN
   v_file := UTL_FILE.FOPEN('MY_DIR', 'employees.txt', 'A');
   UTL_FILE.PUT_LINE(v_file, 'Report generated on: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'));
   UTL_FILE.FCLOSE(v_file);
END;

This adds a timestamp to the file without deleting the existing data.

Handling Errors in UTL_FILE

UTL_FILE can raise various exceptions that need to be handled properly.

Some common exceptions include:

• UTL_FILE.INVALID_PATH – Invalid directory path.
• UTL_FILE.INVALID_MODE – Incorrect mode specified.
• UTL_FILE.INVALID_FILEHANDLE – File handle is invalid.
• NO_DATA_FOUND – End of file reached during read.
• WRITE_ERROR – Issue while writing to a file.

Example with Error Handling

DECLARE
   v_file   UTL_FILE.FILE_TYPE;
   v_line   VARCHAR2(4000);
BEGIN
   v_file := UTL_FILE.FOPEN('MY_DIR', 'missing.txt', 'R');
   UTL_FILE.GET_LINE(v_file, v_line);
   UTL_FILE.FCLOSE(v_file);
EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN
      DBMS_OUTPUT.PUT_LINE('Invalid directory path.');
   WHEN UTL_FILE.INVALID_MODE THEN
      DBMS_OUTPUT.PUT_LINE('Invalid mode used for file.');
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Reached end of file.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;

This ensures that errors are caught gracefully, instead of crashing the program.

Best Practices for Using UTL_FILE

1. Always close files – Leaving files open can cause memory leaks and lock issues.
2. Use exceptions wisely – Trap errors to avoid unhandled failures.
3. Limit file access – Grant users only the necessary privileges.
4. Use directory objects – Avoid UTL_FILE_DIR as it is deprecated and less secure.
5. Validate inputs – Ensure file names and paths are safe to prevent injection attacks.
6. Use buffer sizes carefully – Keep in mind the default maximum line size (32767 bytes in Oracle 12c+).

Conclusion

The UTL_FILE package in PL/SQL is a powerful tool for handling file operations from within Oracle databases. It allows you to create reports, export and import data, and maintain logs directly through PL/SQL code. By learning how to open, read, write, append, and handle errors effectively, developers can integrate file processing seamlessly into their applications.