3

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;
Community
  • 1
  • 1
Jemru
  • 2,091
  • 16
  • 39
  • 52
  • 1
    Blobs are meant for binary data, and you're seeing that, not compressed format. What results are you expecting? have a look at http://stackoverflow.com/questions/828650/how-do-i-get-textual-contents-from-blob-in-oracle-sql?rq=1 – Sathyajith Bhat Jun 18 '14 at 05:19
  • Hi Sathya, Thanks for the reply. Please see Update 1 of example of what I want to get. In the one of the front end of the application, of which I cannot access codes, it does display string correctly. We are creating another front end for that application, and I can't extract the values of some blobs correctly. Thank you. – Jemru Jun 18 '14 at 05:32
  • Given that there any number of compression algorithms in the world, if your data is actually compressed, you'd need to tell us what algorithm was used before we could tell you how to uncompress it. To @Sathya's point, however, I'm not convinced that the data is actually compressed. – Justin Cave Jun 18 '14 at 05:32
  • Hi Justin Cave, thanks for your reply. Please see my Update 2. There is additonal column that tells if it is compressed or not. Unfortunately the application have no documentation and no transition given so I have to figure it out on my own. Thank you. – Jemru Jun 18 '14 at 05:39
  • 2
    No one can possibly tell you how some unknown application compressed a piece of data. Therefore, no one can tell you how to uncompress it. You'd need to look at the code for this existing application or start randomly trying various compression algorithms via trial and error. – Justin Cave Jun 18 '14 at 06:09
  • Hi Justin Cave, ok thank you for your advice. – Jemru Jun 18 '14 at 06:19

2 Answers2

3

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>;
Nawed Shaikh
  • 419
  • 5
  • 22
0

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;
Chris Catignani
  • 5,040
  • 16
  • 42
  • 49
Surya
  • 3
  • 2