PL/SQL UTL_FILE.FILE_TYPE

UTL_FILE.FILE_TYPE is a data type used to represent a file handle or pointer to a file. It allows you to open a file, perform various file operations like reading, writing, and closing, and handle files that are located in directories on the database server. The directory where files are accessed must be defined in Oracle using directory objects or specified in the initialization parameter UTL_FILE_DIR (though this method is deprecated in newer Oracle versions).

Main Operations on UTL_FILE.FILE_TYPE

The UTL_FILE.FILE_TYPE allows you to interact with files in the following ways:

Opening a File: To open a file, you use UTL_FILE.FOPEN or UTL_FILE.FOPEN_NCHAR, which returns a file handle of type UTL_FILE.FILE_TYPE.

DECLARE
  file_handle UTL_FILE.FILE_TYPE;
BEGIN
  file_handle := UTL_FILE.FOPEN('MY_DIR', 'myfile.txt', 'W');
END;

The FOPEN function takes three arguments:

The directory object (MY_DIR in this case).
The file name (myfile.txt).
The mode, which can be ‘R’ for reading, ‘W’ for writing, or ‘A’ for appending.

Writing to a File: You can write data to a file using the PUT or PUT_LINE procedure.

UTL_FILE.PUT_LINE(file_handle, 'Hello, this is a line of text.');

PUT_LINE appends a newline character at the end of the text, while PUT does not.

Reading from a File: If the file is opened in read mode, you can read lines from it using GET_LINE:

DECLARE
  line_content VARCHAR2(100);
BEGIN
  UTL_FILE.GET_LINE(file_handle, line_content);
  DBMS_OUTPUT.PUT_LINE(line_content);
END;

Closing a File: After finishing file operations, you must close the file using the FCLOSE procedure:

UTL_FILE.FCLOSE(file_handle);

Error Handling: While working with files, it is essential to handle errors properly using exceptions like UTL_FILE.INVALID_PATH, UTL_FILE.INVALID_MODE, UTL_FILE.INVALID_OPERATION, etc.

Key Points

File Handle (UTL_FILE.FILE_TYPE): A file handle is a pointer to the file you are working with, and all operations are performed using this handle.

File Access Control: The directory paths accessible via UTL_FILE are controlled through directory objects or the UTL_FILE_DIR parameter (deprecated). In modern Oracle versions, directory objects are more commonly used for security reasons.

File Modes: You can open a file in read (‘R’), write (‘W’), or append (‘A’) mode.

Example of File Write Operation

DECLARE
  file_handle UTL_FILE.FILE_TYPE;
BEGIN
  -- Open file in write mode
  file_handle := UTL_FILE.FOPEN('MY_DIR', 'example.txt', 'W');

  -- Write to the file
  UTL_FILE.PUT_LINE(file_handle, 'This is an example text.');
  UTL_FILE.PUT_LINE(file_handle, 'Writing another line.');

  -- Close the file
  UTL_FILE.FCLOSE(file_handle);
END;

Example of File Read Operation

DECLARE
  file_handle UTL_FILE.FILE_TYPE;
  line_content VARCHAR2(100);
BEGIN
  -- Open file in read mode
  file_handle := UTL_FILE.FOPEN('MY_DIR', 'example.txt', 'R');

  -- Read each line and output it
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(file_handle, line_content);
      DBMS_OUTPUT.PUT_LINE(line_content);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;  -- Exit the loop when end of file is reached
    END;
  END LOOP;

  -- Close the file
  UTL_FILE.FCLOSE(file_handle);
END;

Error Handling Example

DECLARE
  file_handle UTL_FILE.FILE_TYPE;
BEGIN
  BEGIN
    file_handle := UTL_FILE.FOPEN('INVALID_DIR', 'file.txt', 'R');
  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 file mode.');
  END;
END;

In summary, UTL_FILE.FILE_TYPE is a critical component of the UTL_FILE package for file operations in PL/SQL. It allows developers to open, read, write, and close files on the Oracle server’s file system in a structured and controlled manner. Proper error handling is essential to avoid file-related issues during execution.