My Oracle Support Banner

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

Last updated on OCTOBER 20, 2019

Applies to:

PL/SQL - Version 9.2.0.8 and later
Information in this document applies to any platform.
***Checked for relevance on 17-Jan-2019***


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

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
Purpose
Scope
Details
References

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