How to retrieve text stored in BLOB column in ORACLE 11g using SQL?

Question

I have compressed json text stored in BLOB column in Oracle 11g.

Is it possible to retrieve it using SQL only?

EDIT:

AFAIK the data was compressed on Linux OS using ZLIB and loaded using dbms_lob.loadfromfile

How was the data compressed? How was it stored in the BLOB column? Did you use utl_compress, for example, to compress the data? Or was the data compressed outside of PL/SQL? If so, what algorithm was used? — Justin Cave
– Justin Cave, Commented Oct 23, 2012 at 14:32

Justin Cave · Accepted Answer · 2012-10-23 15:17:28Z

1

Oracle doesn't provide any built-in functions that would uncompress a ZLIB-compressed stream (though utl_compress uses very, very similar algorithms).

You would realistically need to load one of the various Java libraries that uncompresses a ZLIB-compressed stream into the database, write a bit of code to wrap that library, and then call that library from SQL. This wouldn't be a pure SQL implementation.

If you're really ambitious, it should be possible to implement the DEFLATE algorithm in pure SQL though that would likely be exceedingly painful SQL to write (or debug or maintain).

answered Oct 23, 2012 at 15:17

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to retrieve text stored in BLOB column in ORACLE 11g using SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related