Wednesday 2 January 2008

Oracle 10g UTL_COMPRESS

Oracle 10g includes many PL/SQL enhancements including: utl_compress
The UTL_COMPRESS package provides an API to allow compression and decompression of binary data (RAW, BLOB and BFILE). It uses the Lempel-Ziv compression algorithm which is equivalent to functionality of the gzip utility. A simple example of it's usage would be:

SET SERVEROUTPUT ON
DECLARE
l_original_blob BLOB;
l_compressed_blob BLOB;
l_uncompressed_blob BLOB;
BEGIN
-- Initialize both BLOBs to something.
l_original_blob := TO_BLOB(UTL_RAW.CAST_TO_RAW('1234567890123456789012345678901234567890'));
l_compressed_blob := TO_BLOB('1');
l_uncompressed_blob := TO_BLOB('1');

-- Compress the data.
UTL_COMPRESS.lz_compress (src => l_original_blob,
dst => l_compressed_blob);

-- Uncompress the data.
UTL_COMPRESS.lz_uncompress (src => l_compressed_blob,
dst => l_uncompressed_blob);

-- Display lengths.
DBMS_OUTPUT.put_line('Original Length : ' || LENGTH(l_original_blob));
DBMS_OUTPUT.put_line('Compressed Length : ' || LENGTH(l_compressed_blob));
DBMS_OUTPUT.put_line('Uncompressed Length: ' || LENGTH(l_uncompressed_blob));

-- Free temporary BLOBs.
DBMS_LOB.FREETEMPORARY(l_original_blob);
DBMS_LOB.FREETEMPORARY(l_compressed_blob);
DBMS_LOB.FREETEMPORARY(l_uncompressed_blob);
END;
/