The UTL_FILE.PUT procedure is part of the UTL_FILE package in Oracle PL/SQL, which is used to perform file I/O operations such as reading from and writing to files in the server’s file system. The PUT procedure specifically allows writing text data to a file.
Syntax
UTL_FILE.PUT ( file_handle IN UTL_FILE.FILE_TYPE, buffer IN VARCHAR2 );
file_handle: This is the file pointer or handle that references the file to which data will be written. It is obtained through the UTL_FILE.FOPEN function, which opens the file.
buffer: This is the actual text data that will be written to the file. It must be a VARCHAR2 string.
Key Characteristics
No automatic newline: UTL_FILE.PUT does not automatically append a newline character at the end of the written data. If you want to write a line of text and move to the next line, you must use the UTL_FILE.NEW_LINE procedure in combination with UTL_FILE.PUT.
Character set limitations: The buffer string is written using the character set of the database. If your file system or application expects a different character set, you may need to convert the string appropriately.
Example
DECLARE
file_handle UTL_FILE.FILE_TYPE;
BEGIN
-- Open a file in write mode (w)
file_handle := UTL_FILE.FOPEN('MY_DIR', 'example.txt', 'w');
-- Write a string to the file
UTL_FILE.PUT(file_handle, 'Hello, Oracle PL/SQL!');
-- Add a newline after the string
UTL_FILE.NEW_LINE(file_handle);
-- Write another string
UTL_FILE.PUT(file_handle, 'This is another line.');
-- Close the file
UTL_FILE.FCLOSE(file_handle);
END;
Key Notes
File access restrictions: Oracle’s UTL_FILE package can only access directories that have been explicitly defined in the database using a directory object (e.g., MY_DIR in the example). The directory object must be created by a DBA using the CREATE DIRECTORY command.
Error handling: Always use exception handling in file operations since there are many possible errors (like file not found, permission issues, etc.). You can trap file I/O exceptions using Oracle’s predefined exceptions like UTL_FILE.INVALID_PATH, UTL_FILE.WRITE_ERROR, etc.
Maximum line size: UTL_FILE.PUT can write up to 32767 characters in a single call. If the buffer exceeds this limit, Oracle will raise an exception.
Example with Exception Handling
DECLARE
file_handle UTL_FILE.FILE_TYPE;
BEGIN
file_handle := UTL_FILE.FOPEN('MY_DIR', 'example.txt', 'w');
UTL_FILE.PUT(file_handle, 'Writing some text');
UTL_FILE.NEW_LINE(file_handle);
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Invalid file path.');
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error writing to file.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
Related Procedures
UTL_FILE.NEW_LINE: Writes a platform-specific newline character to the file.
UTL_FILE.FOPEN: Opens a file for reading or writing.
UTL_FILE.FCLOSE: Closes an open file handle.
By using UTL_FILE.PUT, you can efficiently manage writing operations in PL/SQL, especially when generating reports, logs, or exporting data from the Oracle database to external files.