Oracle TO_LOB Function: A Simple Guide

The TO_LOB function in Oracle SQL is a very specialized function used for data migration. Its one and only purpose is to convert old, deprecated LONG and LONG RAW data types into modern LOB (Large Object) data types, such as CLOB or BLOB.

Note: The LONG and LONG RAW data types have not been recommended for new applications since Oracle8i. This function exists almost exclusively to help you migrate data from old tables to new ones.

What is the TO_LOB Function in Oracle?

The TO_LOB(long_column) function takes a LONG or LONG RAW column as input and converts its contents into a CLOB or BLOB, respectively.

• LONG (large text) is converted to CLOB (Character Large Object).
• LONG RAW (large binary data) is converted to BLOB (Binary Large Object).

CRITICAL: Syntax Restrictions

This function is not a general-purpose function like TO_CHAR or TO_DATE. It has very strict usage rules:

1. It can only be used on a LONG or LONG RAW column.
2. It can only be used in the SELECT list of a subquery inside an INSERT statement.
3. You cannot use this function in a simple SELECT statement (e.g., SELECT TO_LOB(my_long_col) FROM my_table; will fail).
4. You cannot use it in PL/SQL (use TO_CLOB or TO_BLOB inside PL/SQL instead).

TO_LOB Function Syntax

The syntax for TO_LOB is:

TO_LOB(long_column)

Let's break that down:

• long_column: This must be the name of a column with the LONG or LONG RAW data type.

Oracle TO_LOB Function Examples

Because this function can only be used in an INSERT ... SELECT statement, these examples show the full migration process. These are hypothetical examples.

Example 1: Migrating a LONG column to a CLOB with TO_LOB

This example shows how to copy text data from an old LONG column into a new CLOB column.

Step 1: The old and new tables. First, you would have an old table, and you would create a new table with a CLOB column.

-- This is our OLD table
CREATE TABLE old_articles (
  article_id   NUMBER,
  article_text LONG -- The deprecated data type
);

-- This is our NEW table
CREATE TABLE new_articles (
  article_id     NUMBER,
  article_clob   CLOB  -- The new, modern data type
);

Step 2: The INSERT statement using TO_LOB. This is the only place you can use the TO_LOB function.

-- This query selects from the old table and inserts into the new one.
-- TO_LOB converts the LONG data to CLOB during the insert.
INSERT INTO new_articles (article_id, article_clob)
SELECT 
  article_id, 
  TO_LOB(article_text) 
FROM 
  old_articles;

Example 2: Migrating a LONG RAW column to a BLOB with TO_LOB

This example shows how to copy binary data from an old LONG RAW column into a new BLOB column.

Step 1: The old and new tables. First, you would have an old table with binary data, and you would create a new table with a BLOB column.

-- This is our OLD table
CREATE TABLE old_files (
  file_id   NUMBER,
  file_data LONG RAW -- The deprecated data type
);

-- This is our NEW table
CREATE TABLE new_files (
  file_id     NUMBER,
  file_blob   BLOB  -- The new, modern data type
);

Step 2: The INSERT statement using TO_LOB. This query converts the LONG RAW data to BLOB during the insert.

INSERT INTO new_files (file_id, file_blob)
SELECT 
  file_id, 
  TO_LOB(file_data) 
FROM 
  old_files;