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 18.104.22.168 and later
Information in this document applies to any platform.
***Checked for relevance on 17-Jan-2019***
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 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.
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