Oracle TO_BLOB (raw) Function: A Simple Guide

The TO_BLOB (raw) function in Oracle SQL is a conversion function that "upgrades" a RAW or LONG RAW data type into a BLOB (Binary Large Object).

This is useful when you have a smaller binary value (stored in a RAW column) that you need to insert or treat as a BLOB, which is designed to hold much larger binary data (up to several gigabytes).

Note: The LONG RAW data type is deprecated. Oracle strongly recommends using BLOB for all new applications. This function is often used to help migrate data from LONG RAW to BLOB.

What is the TO_BLOB (raw) Function in Oracle?

The TO_BLOB(raw_value) function takes a RAW or LONG RAW value as input and returns its contents as a BLOB data type.

• Input: A RAW or LONG RAW value or column.
• Output: A BLOB data type.

TO_BLOB (raw) Function Syntax

The syntax for TO_BLOB (raw) is very simple:

TO_BLOB(raw_value)

Let's break that down:

• raw_value: The RAW or LONG RAW data you want to convert to a BLOB.

Oracle TO_BLOB (raw) Function Examples

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

Example 1: Converting a RAW Value to a BLOB with TO_BLOB

This example shows how to convert a simple RAW value into a BLOB. We will use HEXTORAW to create the initial RAW value.

Query:

-- This query creates a RAW value from hex
-- and then converts that RAW value into a BLOB
SELECT 
  TO_BLOB(HEXTORAW('48656C6C6F')) AS "Blob_Value"
FROM DUAL;

Result: (The output will be a BLOB data type. Many SQL tools will just display (BLOB) to represent it.)

Blob_Value
----------
(BLOB)

Example 2: Migrating a RAW Column to a BLOB Column with TO_BLOB

This is the most common use case. Imagine you have an old table (old_raw_table) with a RAW column and you want to copy that data into a new table (new_blob_table) with a BLOB column.

Query: (This is a hypothetical example of an INSERT statement.)

-- Create a new table with a BLOB column
CREATE TABLE new_blob_table (
  id NUMBER,
  blob_data BLOB
);

-- Copy data from the old table into the new one,
-- using TO_BLOB to convert the data type
INSERT INTO new_blob_table (id, blob_data)
SELECT 
  id,
  TO_BLOB(raw_column) -- Converting the RAW column to a BLOB
FROM 
  old_raw_table;

Result: (This would copy all the rows from old_raw_table into new_blob_table, successfully converting the RAW data to BLOB data in the process.)