Oracle EMPTY_BLOB Function: A Simple Guide

The EMPTY_BLOB() function in Oracle SQL is a special function used to create an "empty" BLOB (Binary Large Object).

This is a crucial first step when you want to add large binary data (like an image or a file) to a table. You typically don't insert the file directly. Instead, you first insert an EMPTY_BLOB() locator, and then you use other tools (like PL/SQL's DBMS_LOB package) to load your file's data into that empty locator.

What is the EMPTY_BLOB Function in Oracle?

The EMPTY_BLOB() function returns a BLOB locator that is initialized but not populated with data.

This is a very important concept:

• A NULL BLOB is a complete absence of a value. It's a "black hole." You cannot write data to it.
• An EMPTY_BLOB() BLOB is an initialized, empty container. It's an "empty box" that is ready to be filled with data.

You must use EMPTY_BLOB() to initialize a BLOB column before you can write data to it with most LOB-writing tools.

EMPTY_BLOB Function Syntax

The syntax for EMPTY_BLOB is very simple, as it takes no arguments:

EMPTY_BLOB()

This function is used almost exclusively in the SET clause of an UPDATE statement or the VALUES clause of an INSERT statement.

Oracle EMPTY_BLOB Function Examples

Here are two practical examples of how to use EMPTY_BLOB.

Example 1: Inserting a Row with EMPTY_BLOB

This example shows how to insert a new row for an employee and initialize their profile_picture column with an EMPTY_BLOB() so it can be populated later.

Query: (This assumes you have a table employees with a BLOB column profile_picture)

INSERT INTO employees (employee_id, last_name, profile_picture)
VALUES (
  207, 
  'Smith', 
  EMPTY_BLOB()
);

Result: (This creates a new row for Smith, and the profile_picture column is now an empty, non-null BLOB that is ready to be written to.)

1 row created.

Example 2: Updating a Row to Have an EMPTY_BLOB

This example shows how to initialize the ad_photo column for an existing product in the print_media table. This prepares the row for a photo to be uploaded.

Query:

UPDATE print_media
SET 
  ad_photo = EMPTY_BLOB()
WHERE 
  product_id = 3000;

Result: (This updates the row and makes the ad_photo column an empty, writeable BLOB instead of NULL.)

1 row updated.