Moving Data From Varchar2 to a Blob Column -

Introduction

I recently had a scenario where a developer after developing an application that had a text box needed to allow that text box to contain attachments and even pictures and thus had to change that text box column from a VARCHAR2 column to a BLOB. However, The column already had data in it. This blog post serves to share the steps taken to move data from a VARCHAR2 column to a BLOB column and some of the issues faced.

Demonstration

Create table and sample data

The code below creates the tables and sample data needed for this demonstration.

--drop tables if they exist
DROP TABLE IF EXISTS
t_blob
/
DROP TABLE IF EXISTS
t_clob
/
DROP TABLE IF EXISTS
t_varchar2
/

--create varchar2 table
CREATE TABLE t_varchar2
( user_id     NUMBER
,user_details VARCHAR2(200 CHAR)
);

--create blob table
CREATE TABLE t_blob
(user_id      NUMBER
,user_details BLOB
);

-- create clob table
CREATE TABLE t_clob
( user_id     NUMBER
,user_details CLOB
);

-- add data to varchar2 table
INSERT INTO t_varchar2
SELECT 1, 'Today is the 20th of July 2026 and the weather is warm and sunny in Winnipeg, Manitoba'
FROM DUAL;

CLOB or BLOB

The decision on whether to use a CLOB or BLOB is interesting. Both are large object storage, one for character and another for binary data.

CLOBs are ideal for:

When the data consists of text characters that Oracle needs to interpret
When the data may need to be searched using text based functions
Data that may require character set conversion
HTML, CSV, XML, logs, large plain text documents.

Meanwhile, BLOBs are ideal for :

Binary data
When no character set conversion is required
Data that will not need to be searched using text based search functions
images, videos, pdf, zip files and compiled code.

Can Character data fit in a BLOB?

As one of my favorite oracle technologists will always answer to questions… “Yes and No”. Let’s examine the finer details:

We query our VARCHAR2 table:

SELECT * 
FROM t_varchar2;

   USER_ID USER_DETAILS
---------- ----------------------------------------------------------------------------------------------------
	 1 Today is the 20th of July 2026 and the weather is warm and sunny in Winnipeg, Manitoba

We then attempt to insert this data directly into a table with a BLOB column:

INSERT INTO t_blob
SELECT
       user_id
      ,user_details
FROM 
     t_varchar2;

ERROR at line 4:
ORA-01465: invalid hex number
Help: https://docs.oracle.com/error-help/db/ora-01465/

We get an ORA-01465 error. We are unable to insert data directly from a VARCHAR2 column to a BLOB column. Well, not directly anyway! There is an inbuilt function in Oracle that helps us perform this conversion.

UTL_RAW.CAST_TO_RAW

Using the function UTL_RAW.CAST_TO_RAW, we are able to onvert a VARCHAR2 value represented using some number of data bytes into a RAW value with that number of data bytes.

If we attempt our insert statement again, wrapping our source column around this function, we are able to insert the row successfully:

INSERT INTO t_blob(user_id, user_details)
SELECT user_id, UTL_RAW.cast_to_raw (user_details)
FROM t_varchar2 ;

1 row created.

Are we able to retrieve the data?

The data now resides in a BLOB column. However, there is the issue of retrieving it! Does a simple query return textual or binary data?

We query the t_blob table:

SELECT * FROM t_blob ;

   USER_ID USER_DETAILS
---------- ----------------------------------------------------------------------------------------------------
	 1 546F646179206973207468652032307468206F66204A756C79203230323620616E6420746865207765617468657220697320
	   7761726D20616E642073756E6E7920696E2057696E6E697065672C204D61

We get data in binary format! However, what we actually want to see is the initial text. We can query for that text string using the function: UTL_RAW.CAST_TO_VARCHAR2:

SELECT
      user_id
     ,utl_raw.cast_to_varchar2(user_details) user_details
FROM
     t_blob ;

   USER_ID USER_DETAILS
---------- ----------------------------------------------------------------------------------------------------
	 1 Today is the 20th of July 2026 and the weather is warm and sunny in Winnipeg, Manitoba

What about a CLOB?

Data from a VARCHAR2 column fits nicely into a CLOB column without requiring any conversion functions as shown below:

SET LONG 1000

INSERT INTO t_clob
SELECT user_id, user_details
FROM t_varchar2 ;

1 row created.

SELECT * from t_clob ;

   USER_ID USER_DETAILS
---------- ----------------------------------------------------------------------------------------------------
	 1 Today is the 20th of July 2026 and the weather is warm and sunny in Winnipeg, Manitoba

It is also not possible to insert data directly from the CLOB column to the BLOB column;

INSERT INTO t_blob
SELECT * FROM t_clob ;

ERROR at line 2:
ORA-00932: expression is of data type CLOB, which is incompatible with expected data type BLOB
Help: https://docs.oracle.com/error-help/db/ora-00932/

using the function utl_raw.cast_to_raw, the insert statement works :

INSERT INTO t_blob
SELECT
  user_id
 ,utl_raw.cast_to_raw(user_details)
FROM t_clob
/

1 row created.

Conclusion

Data stored in a VARCHAR2 column can be moved into a BLOB column when converted to binary using utl_raw.cast_to_raw. An reverse utl_raw.cast_to_varchar is required to read the data as text upon retrieval. We also examined in this post that CLOB columns do not have any of these limitations. Something to remember if ever tasked with moving data to BLOB columns. Hopefully you found this useful and thanks for reading.
Harris.

Moving Data From Varchar2 to a Blob Column