PL/SQL UTL_FILE.PUT_LINE

The UTL_FILE.PUT_LINE procedure in Oracle PL/SQL is used for writing a line of text to a file on the server’s filesystem. It is part of the UTL_FILE package, which provides methods to read and write text files stored on the Oracle database server. The UTL_FILE.PUT_LINE procedure writes a string (text) followed by a newline character to a specified file.

Syntax

UTL_FILE.PUT_LINE (
   file    IN  UTL_FILE.FILE_TYPE,
   buffer  IN  VARCHAR2
);

Parameters:

file: This is a file handle, which refers to the file that is opened using UTL_FILE.FOPEN or UTL_FILE.FOPEN_NCHAR. It is of type UTL_FILE.FILE_TYPE.

buffer: This is the text (a string of up to 32767 characters) that will be written to the file, followed by a newline.

Example

Here’s an example that demonstrates how to use UTL_FILE.PUT_LINE to write a line of text to a file:

DECLARE
   file_handle UTL_FILE.FILE_TYPE;
BEGIN
   -- Open the file in write mode
   file_handle := UTL_FILE.FOPEN('DATA_DIR', 'example.txt', 'w');
   
   -- Write a line of text to the file
   UTL_FILE.PUT_LINE(file_handle, 'This is a line of text.');
   
   -- Write another line
   UTL_FILE.PUT_LINE(file_handle, 'Another line of text.');

   -- Close the file
   UTL_FILE.FCLOSE(file_handle);
EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN
      DBMS_OUTPUT.PUT_LINE('Invalid directory or filename.');
   WHEN UTL_FILE.WRITE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Error while writing to the file.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

Explanation:

UTL_FILE.FOPEN: Opens the file example.txt in the DATA_DIR directory for writing. If the directory or file does not exist, an exception will be raised.

UTL_FILE.PUT_LINE: Writes the text to the file followed by a newline.

UTL_FILE.FCLOSE: Closes the file after writing.

Key Points

Directory Objects: The UTL_FILE.FOPEN procedure requires a directory object (DATA_DIR in the example above), which is created by the DBA to specify the location where files are read from or written to. Example:

CREATE OR REPLACE DIRECTORY DATA_DIR AS '/path/to/your/directory';

The Oracle user must also have appropriate permissions on the directory.

Exceptions: There are several predefined exceptions related to file handling, such as INVALID_PATH (invalid directory or file) and WRITE_ERROR (error while writing to the file). These should be handled properly to ensure robustness.

Buffer Limitation: The buffer parameter can contain a maximum of 32,767 characters.

Permissions: The Oracle database process must have operating system-level write permissions to the directory where the file is being created or modified.

UTL_FILE.PUT_LINE is useful in cases where PL/SQL code needs to generate reports, export data, or write logs to the filesystem. However, it should be used with caution, especially regarding file permissions and paths, to avoid security issues.