Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: LOB Compression
DA Morgan wrote:
>
> Glad to do so if you send the data to be loaded and compressed.
Thanks Daniel,
Below is a java package, stolen from Tom Kyte's website, some pl/sql code that should automatically test what I'm asking and finally the results I expect to see:
I would like it tested outside of my environment to ensure that it's not a misconfiguration on my part.
Java Code:
create or replace and compile java source named LobCompressor as
import java.lang.*;
import oracle.sql.*;
import java.io.*; import java.util.zip.InflaterInputStream; import java.util.zip.DeflaterOutputStream;
/**
int cnt; while ((cnt=in.read(buffer))!=-1) { z.write(buffer,0,cnt); } in.close(); z.close();
/**
int cnt; while ((cnt=z.read(buffer))!=-1) { out.write(buffer,0,cnt); } z.close(); out.close();
/**
int cnt; while ((cnt=in.read(buffer))!=-1) { z.write(buffer,0,cnt); } in.close(); z.close();
/**
int cnt; while ((cnt=z.read(buffer))!=-1) { out.write(buffer,0,cnt); } z.close(); out.close();
PL/SQL package specification:
create or replace package compressor is
function clob_compress(p_clob clob) return blob; function clob_decompress(p_blob blob) return clob; function blob_compress(p_blob blob) return blob; function blob_decompress(p_blob blob) return blob;end;
And PL/SQL package implementation:
create or replace package body compressor is
procedure clob_decompress(p_blob blob, p_clob clob)
as language java
name 'LobCompressor.decompress(oracle.sql.BLOB, oracle.sql.CLOB)';
procedure clob_compress(p_clob clob, p_blob blob)
as language java
name 'LobCompressor.compress(oracle.sql.CLOB, oracle.sql.BLOB)';
procedure blob_decompress(p_slob blob, p_blob blob)
as language java
name 'LobCompressor.decompress(oracle.sql.BLOB, oracle.sql.BLOB)';
procedure blob_compress(p_slob blob, p_blob blob)
as language java
name 'LobCompressor.compress(oracle.sql.BLOB, oracle.sql.BLOB)';
function clob_compress(p_clob clob) return blob is
l_blob blob;
begin
if p_clob is null then return null; end if; dbms_lob.createtemporary(l_blob,true); clob_compress(p_clob,l_blob); return l_blob;
function clob_decompress(p_blob blob) return clob is
l_clob clob;
begin
if p_blob is null then return null; end if; dbms_lob.createtemporary(l_clob,true); clob_decompress(p_blob,l_clob); return l_clob;
function blob_compress(p_blob blob) return blob is
l_blob blob;
begin
if p_blob is null then return null; end if; dbms_lob.createtemporary(l_blob,true); blob_compress(p_blob,l_blob); return l_blob;
function blob_decompress(p_blob blob) return blob is
l_blob blob;
begin
if p_blob is null then return null; end if; dbms_lob.createtemporary(l_blob,true); blob_decompress(p_blob,l_blob); return l_blob;
end;
/
Pl/SQL Code:
DECLARE
rawtext raw(32767);
uncompressed blob; compressed1 blob; compressed2 blob; uncompressed1 blob; uncompressed2 blob;
dbms_lob.createtemporary(uncompressed, TRUE); dbms_lob.open(uncompressed, dbms_lob.lob_readwrite); dbms_lob.writeappend(uncompressed, utl_raw.length(rawtext), rawtext); dbms_output.put_line('Uncompressed Length: ' || dbms_lob.getlength(uncompressed));
compressed1 := compressor.blob_compress(uncompressed);
dbms_output.put_line('Java Compressed Length: '
|| dbms_lob.getlength(compressed1));
compressed2 := utl_compress.lz_compress(uncompressed, 1);
dbms_output.put_line('UTL Compressed Length: '
|| dbms_lob.getlength(compressed2));
uncompressed1 := compressor.blob_decompress(compressed1);
dbms_output.put_line('Java Decompressed Length: '
|| dbms_lob.getlength(uncompressed1));
uncompressed2 := utl_compress.lz_uncompress(compressed2);
dbms_output.put_line('UTL Uncompressed Length: '
|| dbms_lob.getlength(uncompressed2));
dbms_lob.freetemporary(uncompressed1); dbms_lob.freetemporary(uncompressed2);
BEGIN
uncompressed1 := compressor.blob_decompress(compressed2);
dbms_output.put_line('UTL Decompressed by Java Length: ' || dbms_lob.getlength(uncompressed1)); dbms_lob.freetemporary(uncompressed2); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error encountered decompressing UTL ' || 'Compressed BLOB with Java Decompress (' || SQLCODE || '): ' || SQLERRM);END; BEGIN
dbms_output.put_line('Java Uncompressed by UTL Length: ' || dbms_lob.getlength(uncompressed2)); dbms_lob.freetemporary(uncompressed1); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error encountered decompressing Java ' || 'Compressed BLOB with UTL Uncompress (' || SQLCODE || '): ' || SQLERRM);END; dbms_lob.freetemporary(compressed1);
dbms_lob.close(uncompressed);
dbms_lob.freetemporary(uncompressed);
END;
/
Expected Output:
Uncompressed Length: 104
Java Compressed Length: 90
UTL Compressed Length: 102
Java Decompressed Length: 104
UTL Uncompressed Length: 104
Error encountered decompressing UTL Compressed BLOB with Java Decompress
(-29532): ORA-29532: Java call terminated by uncaught Java exception:
java.util.zip.ZipException: unknown compression method
Error encountered decompressing Java Compressed BLOB with UTL Uncompress
(-29294): ORA-29294: A data error occurred during compression or
uncompression.
Received on Wed Jul 05 2006 - 11:51:25 CDT
![]() |
![]() |