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
is
l_blob blob ;
begin

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);

end;
/   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;

Advertisements

Create a free website or blog at WordPress.com.