Mani's Blog

January 12, 2012

How to view gzipped entry XML in Oracle via Client

Filed under: Uncategorized — mani @ 1:11 am

In our database we store XML data in gzipped binary data format.  Column has been defined as RAW(2000).

Following is one of the ways to unzip the data.

select to_clob(rawtohex(entry_part_1))||to_clob(rawtohex(entry_part_2))
from S_1040485_OXFAMN.shape_user_0
where entry_part_2 is not null

To view as uncompressed XML, use following method.
SQL> create or replace function S_1040485_OXFAMN.decode_entry
entry_part_1 raw,
entry_part_2 raw,
entry blob
return blob
l_blob blob ;

if entry is not null then
return utl_compress.lz_uncompress(entry);
end if;
if entry_part_2 is not null then
l_blob := to_blob(entry_part_1);
dbms_lob.append(l_blob, to_blob(entry_part_2));
return utl_compress.lz_uncompress(l_blob);
end if;
return utl_compress.lz_uncompress(entry_part_1);

/   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23

Function created.

I was able to view unzipped XML data from SQL Developer using below SQL.

select S_1040485_OXFAMN.decode_entry(entry_part_1, entry_part_2, entry) xml
from S_1040485_OXFAMN.shape_user_0
where entry_part_2 is not null;


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at

%d bloggers like this: