Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: LOB Compression

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;

/**

        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;

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US