Kevin Crosbie wrote:
> 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;
>
> /**
> * A simple class for LOB compression and decompression in Oracle
> Database. Will
> work in 8i and better.
> *
> * @author <a href="mailto:pjarmuz_at_poczta.onet.pl">Piotr Jarmuz</a>
> */
> public class LobCompressor {
> /**
> * Compresses the CLOB into BLOB
> *
> * @param clob the source CLOB (plain text)
> * @param blob the target BLOB (will hold compressed binary data) it
> should be
> an empty BLOB retrieved for example with
> dbms_lob.createtemporary(l_blob,true);
> * @throws Exception mostly I/O exception if ever
> */
> public static void compress(CLOB clob, BLOB blob) throws Exception {
> InputStream in=clob.getAsciiStream();
> DeflaterOutputStream z=new
> DeflaterOutputStream(blob.getBinaryOutputStream());
> byte[] buffer=new byte[clob.getBufferSize()];
>
> int cnt;
> while ((cnt=in.read(buffer))!=-1) {
> z.write(buffer,0,cnt);
> }
> in.close();
> z.close();
> }
>
> /**
> * Decompresses the BLOB into CLOB
> *
> * @param blob the source BLOB (compressed binary data)
> * @param clob the target CLOB (will hold plain text) it should be an
> empty CLOB
> retrieved for example with dbms_lob.createtemporary(l_clob,true);
> * @throws Exception mostly I/O exception if ever
> */
> public static void decompress(BLOB blob, CLOB clob) throws Exception {
> OutputStream out=clob.getAsciiOutputStream();
> InflaterInputStream z=new
> InflaterInputStream(blob.getBinaryStream());
> byte[] buffer=new byte[blob.getBufferSize()];
>
> int cnt;
> while ((cnt=z.read(buffer))!=-1) {
> out.write(buffer,0,cnt);
> }
> z.close();
> out.close();
> }
>
> /**
> * Compresses the BLOB into BLOB
> *
> * @param slob the source BLOB (plain binary data)
> * @param blob the target BLOB (will hold compressed binary data) it
> should be
> an empty BLOB retrieved for example with
> dbms_lob.createtemporary(l_blob,true);
> * @throws Exception mostly I/O exception if ever
> */
> public static void compress(BLOB slob, BLOB blob) throws Exception {
> InputStream in=slob.getBinaryStream();
> DeflaterOutputStream z=new
> DeflaterOutputStream(blob.getBinaryOutputStream());
> byte[] buffer=new byte[slob.getBufferSize()];
>
> int cnt;
> while ((cnt=in.read(buffer))!=-1) {
> z.write(buffer,0,cnt);
> }
> in.close();
> z.close();
> }
>
> /**
> * Decompresses the BLOB into CLOB
> *
> * @param blob the source BLOB (compressed binary data)
> * @param slob the target CLOB (will hold plain binary data) it should
> be an
> empty CLOB retrieved for example with dbms_lob.createtemporary(l_blob,true);
> * @throws Exception mostly I/O exception if ever
> */
> public static void decompress(BLOB blob, BLOB slob) throws Exception {
> OutputStream out=slob.getBinaryOutputStream();
> InflaterInputStream z=new
> InflaterInputStream(blob.getBinaryStream());
> byte[] buffer=new byte[blob.getBufferSize()];
>
> 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;
> end;
>
> 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;
> end;
>
> 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;
> end;
>
> 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;
>
> end;
> /
>
> Pl/SQL Code:
>
> DECLARE
> rawtext raw(32767);
> uncompressed blob;
> compressed1 blob;
> compressed2 blob;
> uncompressed1 blob;
> uncompressed2 blob;
> BEGIN
>
> -- Set up BLOB
> rawtext := utl_raw.cast_to_raw(
> 'The quick brown fox jumps over the lazy dog, '
> || 'Jack Sprat could eat not fat and his wife could eat no
> lean');
>
> 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
> uncompressed2 := utl_compress.lz_uncompress(compressed1);
>
> 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.freetemporary(compressed2);
>
> 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.
Thanks. But where is the file you want compressed?
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Jul 06 2006 - 10:45:59 CDT
