CODEWITHSUNDEEP
sqloracle-databaseoracle11gblobtype-conversion
Jemru
Jemru

Reputation: 2119

Oracle How to Uncompress Blob

I'm extracting BLOB column from a Oracle table.

Some of the blob values I'm getting are in right string characters.

However some of the blob values are not displaying correct the string. It seems that these rows are "compressed".

Table Name: TableA
Column Name: ID,                Type: Number
Column Name: BLOB_BINARY_VALUE, Type: BLOB
Column Name: BLOB_COMPRESSED,   Type: Number

Here is the SQL I used:

Select BLOB_BINARY_VALUE FROM TableA WHERE ID =  1234567;

I also tried using:

Select TO_BLOB(BLOB_BIN_VALUE) FROM TableA WHERE ID =  1234567;

Example of Wrong Blob value I'm getting: (I trimmed the value below because of it's length)

 öp‘CÇL.aÜÌIëÉ8gbȨ!ƒ™2fÌÀÌ…mc›cÆÌ:0óÆ
:bØ>n€7iض1M9sÊТÉSДaÃ6gvôéÓ@;iÊÜY“Æ

I'm guessing that this BLOB is in compressed format. How can I uncompressed this kind BLOB?

Thank you very much in advance.

UPDATE 1: Example of Correct Blob query which I'm also expecting from others:

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Shell Dlg;}}
\viewkind4\uc1\pard\f0\fs20 MESSAGE HERE MESSAGE HERE MESSAGE HERE.   \par
}

UPDATE 2:

There is another column in the database named "BLOB_COMPRESSED". The rows that have "0"s are the ones displaying correct values. The rows that have "1"s are the ones displaying wrong values. So I'm guessing that the data is compressed.

UPDATE 3: I tried to follow the advise from this link but still getting like compressed string: How do I get textual contents from BLOB in Oracle SQL

Select utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_BIN_VALUE)) 
FROM TableA 
WHERE ID = 1234567;

Upvotes: 3

Views: 19018

Answers (2)

Surya
Surya

Reputation: 13

This is working for us, we compressed the XML payload via Java zip compress and stored in BLOB column called payload:

select utl_compress.lz_uncompress (src => payload) from TABLE_NAME;

Upvotes: 1

Nawed Shaikh
Nawed Shaikh

Reputation: 419

This worked for me :- 

select  
utl_raw.cast_to_varchar2( dbms_lob.substr( utl_compress.lz_uncompress(BLOB_BIN_VALUE), 2000, 1) )
 from TableA  where ID = <ID>;

Upvotes: 3

Related Questions