PL/SQL UTL_FILE.PUT_RAW

The UTL_FILE.PUT_RAW procedure is part of Oracle’s UTL_FILE package, which allows PL/SQL programs to read and write to operating system files. Specifically, PUT_RAW is used to write raw binary data (as opposed to character data) to a file. This is useful when dealing with non-textual data, such as image files or any binary file formats.

Syntax

UTL_FILE.PUT_RAW (
   file   IN  UTL_FILE.FILE_TYPE,
   buffer IN  RAW,
   autoflush IN BOOLEAN DEFAULT FALSE
);

Parameters

file: This is the file handle that identifies the file opened for writing. It must be of the UTL_FILE.FILE_TYPE datatype, which is created using the UTL_FILE.FOPEN procedure.

buffer: A RAW datatype that contains the binary data you want to write to the file.

autoflush: (Optional) A boolean flag that, when set to TRUE, causes the buffer to be automatically flushed to disk after each write operation. By default, this is set to FALSE, meaning that data may be cached until a flush operation (like UTL_FILE.FFLUSH) is explicitly called.

Example

DECLARE
  l_file UTL_FILE.FILE_TYPE;
  l_raw_data RAW(32767);
BEGIN
  -- Open the file for writing in binary mode
  l_file := UTL_FILE.FOPEN('MY_DIR', 'output.bin', 'wb', 32767);
  
  -- Prepare some raw binary data (example)
  l_raw_data := UTL_RAW.CAST_TO_RAW('Example binary data');

  -- Write the raw data to the file
  UTL_FILE.PUT_RAW(l_file, l_raw_data);

  -- Flush the file
  UTL_FILE.FFLUSH(l_file);

  -- Close the file
  UTL_FILE.FCLOSE(l_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 file open mode');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

Important Considerations

Binary Data Handling: Since UTL_FILE.PUT_RAW works with raw binary data, it is crucial to ensure that the file is opened in binary mode (wb) using UTL_FILE.FOPEN, as shown above. Opening a file in text mode could cause issues when writing binary data.

Buffer Size: The size of the buffer provided to UTL_FILE.PUT_RAW is critical. The maximum size for a single RAW variable is 32767 bytes in PL/SQL. Ensure that the buffer does not exceed this limit, or you may encounter errors.

Performance: Setting the autoflush parameter to TRUE can impact performance since each write operation will be flushed to disk immediately. For better performance, it’s often a good practice to accumulate data and flush only periodically.

Error Handling

UTL_FILE.INVALID_OPERATION: If the file was not opened for writing, this error is raised.

UTL_FILE.INVALID_FILEHANDLE: If the file handle is invalid, this error occurs.

UTL_FILE.WRITE_ERROR: If there is an I/O error while writing to the file, this exception is raised.

In summary, UTL_FILE.PUT_RAW is a useful procedure for writing binary data to files from within Oracle PL/SQL, particularly when working with non-character-based data. Proper management of file handles, binary modes, and buffer sizes is critical to its effective use.