My Oracle Support Banner

How to Convert the Data from BLOB to CLOB using PL/SQL (Doc ID 235142.1)

Last updated on JANUARY 30, 2022

Applies to:

PL/SQL - Version and later
Information in this document applies to any platform.


This article provides an example of how to convert the BLOB data to CLOB using PL/SQL. Generally this type of conversion will not make much sense because BLOB column stores Binary information and CLOB stores text information.

In some cases, where the BLOB column has been confirmed to contain Text information e.g. XML, HTML or Plain text, it is generally best to extract the data from BLOB column and copy it into CLOB column. The following conversion procedure can be used.


The example provided was last tested using an Oracle release on Solaris and worked fine without any problem. The example can be easily modified to suit any other scenario. There is no restriction on size of input BLOB in this example. The example will even work with Binary data stored in BLOB column
but conversion to CLOB for such data will produce junk output. Other filtering techniques are required for such conversion which are described in "Oracle Text" documentation.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.