Converting BLOB containing binary data into CLOB
Hi
We are converting a 3rd party application from mysql to Oracle.
I converted the attachments tables that contain a longblob into a BLOB column in Oracle successfully. i.e - all the attachments ( excel,word , jpg , pdf etc ) - are converted and viewed ok.
The problem is that this column needs to be CLOB. The application on Oracle cannot be used with BLOB for these attachments.
Creating new tickets and storing attachments are done with no issues with CLOB.
However - I do not seem to succeed with the converted attachments , that are currently sitting as BLOBs, i.e - I cannot convert them to CLOBs.
I used PL SQL and the conversion "looks" like it completed successfully , but the data is corrupted.
Apparently , you can use PL SQL only if your BLOB contain text data only.
If it contains binary data , the Metalink states ( doc 235142.1 )
+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*.+
What "filtering techniques" are they referring to ? can some one guide me as to how to complete this conversion successfully ?
Our DB version is 11.2 on Solaris SPARC box
Thanks
Orna