Oracle EMPTY_CLOB Function: A Simple Guide

The EMPTY_CLOB() function in Oracle SQL is a special function used to create an "empty" CLOB (Character Large Object).

This is a crucial first step when you want to add large text data (like an article, a large log, or a JSON document) to a table. You typically don't insert the large text directly. Instead, you first insert an EMPTY_CLOB() locator, and then you use other tools (like PL/SQL's DBMS_LOB package) to write your text data into that empty locator.

What is the EMPTY_CLOB Function in Oracle?

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

This is a very important concept:

• A NULL CLOB is a complete absence of a value. It's a "black hole." You cannot write data to it.
• An EMPTY_CLOB() CLOB is an initialized, empty container. It's an "empty box" that is ready to be filled with text.
• An EMPTY_CLOB() is also not the same as a zero-length string ('').

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

EMPTY_CLOB Function Syntax

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

EMPTY_CLOB()

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

Oracle EMPTY_CLOB Function Examples

Here are two practical examples of how to use EMPTY_CLOB.

Example 1: Inserting a Row with EMPTY_CLOB using EMPTY_CLOB

This example shows how to insert a new row for an article and initialize its article_text column with an EMPTY_CLOB() so it can be populated later.

Query: (This assumes you have a table articles with a CLOB column article_text)

INSERT INTO articles (article_id, article_title, article_text)
VALUES (
  101, 
  'New Article', 
  EMPTY_CLOB()
);

Result: (This creates a new row for 'New Article', and the article_text column is now an empty, non-null CLOB that is ready to be written to.)

1 row created.

Example 2: Updating a Row to Have an EMPTY_CLOB using EMPTY_CLOB

This example shows how to initialize the ad_finaltext column for an existing product in the print_media table. This prepares the row for a new description to be loaded.

Query:

UPDATE print_media
SET 
  ad_finaltext = EMPTY_CLOB()
WHERE 
  product_id = 3000;

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

1 row updated.