PL/SQL UTL_FILE.GET_RAW

The UTL_FILE.GET_RAW procedure in Oracle PL/SQL is part of the UTL_FILE package, which provides an API for reading from and writing to operating system text files from within a PL/SQL environment. Specifically, the GET_RAW procedure allows you to read binary data from a file into a RAW variable. This is useful when working with binary files or handling data that is not in text format.

Syntax

UTL_FILE.GET_RAW (
   file     IN   UTL_FILE.FILE_TYPE, 
   buffer   OUT  RAW, 
   len      IN   NUMBER
);

Parameters:

file: This is a file handle obtained through the UTL_FILE.FOPEN or UTL_FILE.FOPEN_NCHAR procedure. It represents the open file from which you want to read the raw data.

buffer: This is an output parameter. It is a RAW datatype that will store the data read from the file.

len: This specifies the number of bytes to read from the file.

Key Considerations

The file must be opened in binary mode using UTL_FILE.FOPEN with a mode parameter of ‘rb’ (read binary) to use GET_RAW.
The buffer parameter can hold up to 32767 bytes of data, but it is subject to the database’s memory and buffer limits.
If the specified length (len) is greater than the remaining bytes in the file, Oracle will read only up to the end of the file.
After reading, the file pointer is advanced by the number of bytes read.

Example

DECLARE
   v_file     UTL_FILE.FILE_TYPE;
   v_buffer   RAW(32767);
   v_len      PLS_INTEGER := 1024; -- Number of bytes to read
BEGIN
   -- Open the file in read-binary mode
   v_file := UTL_FILE.FOPEN('DIRECTORY_ALIAS', 'binary_file.dat', 'rb');
   
   -- Read 1024 bytes of raw data from the file
   UTL_FILE.GET_RAW(v_file, v_buffer, v_len);
   
   -- Close the file
   UTL_FILE.FCLOSE(v_file);
   
   -- Do something with the raw data
   DBMS_OUTPUT.PUT_LINE('Raw data read: ' || UTL_RAW.CAST_TO_VARCHAR2(v_buffer));
EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN
      DBMS_OUTPUT.PUT_LINE('Invalid file path');
   WHEN UTL_FILE.INVALID_MODE THEN
      DBMS_OUTPUT.PUT_LINE('Invalid file mode');
   WHEN UTL_FILE.READ_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('Read error occurred');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

Use Cases

Binary File Reading: You can use GET_RAW to read binary files such as images, documents, or multimedia content.

Data Transfer: It is useful when you need to transfer binary data from a file into a database BLOB (Binary Large Object) column.

Hexadecimal Processing: Since binary data is read into a RAW datatype, it can be processed or converted into hexadecimal for various purposes like auditing or transformation.

Error Handling

Common exceptions to handle when using UTL_FILE.GET_RAW:

UTL_FILE.INVALID_OPERATION: Raised if the file was not opened in the correct mode.

UTL_FILE.READ_ERROR: Raised if an error occurs while reading the file.

UTL_FILE.INVALID_PATH: Raised if the specified directory or file path is invalid.

In summary, UTL_FILE.GET_RAW is a powerful procedure for handling binary file reading within PL/SQL, allowing developers to work with non-text files in an Oracle environment.