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

Purpose

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.

Scope

The example provided was last tested using an Oracle 10.2.0.4 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.

Details

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