Oracle TO_BLOB (bfile) Function: A Simple Guide

The TO_BLOB (bfile) function in Oracle SQL is a conversion function used to move data from an external file into the database. It reads a BFILE (a data type that points to an external file on the server's file system) and converts its contents into a BLOB (Binary Large Object), which is stored inside the database.

This is a key function for loading external binary files, such as images, PDFs, or videos, from the server's disk into a table.

What is the TO_BLOB (bfile) Function in Oracle?

The TO_BLOB(bfile, [mime_type]) function takes a BFILE locator as input and returns a BLOB containing the actual data from that external file.

• Input: A BFILE locator. (You must first create a DIRECTORY object in Oracle and use the BFILENAME function to get this locator).
• Output: A BLOB data type.
• mime_type (Optional): You can also specify a MIME type string (like 'image/jpeg' or 'application/pdf') to store with the BLOB.

TO_BLOB (bfile) Function Syntax

The syntax for TO_BLOB (bfile) is:

TO_BLOB(bfile_value, [mime_type])

Let's break that down:

• bfile_value: The BFILE locator (a pointer) to the external file you want to read.
• [mime_type]: An optional text string specifying the MIME type of the file.

Note: This is an Advanced Function

Using this function requires database-level setup. You must have an Oracle DIRECTORY object created by a DBA (e.g., CREATE DIRECTORY media_dir AS '/u01/app/oracle/media') and have READ permissions on it. The examples below are hypothetical to show how the function is used in a real application.

Oracle TO_BLOB (bfile) Function Examples

Here are two practical examples of how to use TO_BLOB (bfile).

Example 1: Converting a BFILE to a BLOB using TO_BLOB

This hypothetical example assumes you have a table media_tab with a BFILE column named media_col. We want to select the data from that external file as a BLOB.

(This assumes a DIRECTORY object and a BFILE locator already exist.)

Query:

-- Selects the binary content of the external file
-- and assigns the 'image/jpeg' MIME type to the resulting BLOB
SELECT 
  TO_BLOB(media_col, 'image/jpeg') AS "File_Data_As_BLOB"
FROM 
  media_tab
WHERE 
  file_id = 101;

Example 2: Inserting an External File into a BLOB Column using TO_BLOB

This is a more common use case. Imagine you have a table user_profiles with a BLOB column for photos. You want to insert a new user's photo from a file on the server.

(This assumes a DIRECTORY object named USER_PHOTOS_DIR exists and points to a server path.)

Query:

-- Insert a new row, reading the file 'avatar_123.jpg'
-- from the server directory 'USER_PHOTOS_DIR'
INSERT INTO user_profiles (user_id, profile_photo)
VALUES (
  123,
  TO_BLOB(
    BFILENAME('USER_PHOTOS_DIR', 'avatar_123.jpg'), 
    'image/jpeg'
  )
);

In this example, BFILENAME gets the BFILE locator for the file, and TO_BLOB reads that file and inserts its binary data into the profile_photo BLOB column.