Re: LOB Compression

From: Kevin Crosbie <caoimhinocrosbai_at_at_yahoo.com>
Date: Wed, 05 Jul 2006 18:51:25 +0200
Message-ID: <44abed98$0$10357$88260bb3@news-taz.teranews.com>

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. Received on Wed Jul 05 2006 - 11:51:25 CDT