8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

UTL_COMPRESS : Compress and Uncompress Data from PL/SQL

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.

The package contains procedures and functions to perform similar actions.

Related articles.

Procedures

This example uses the LZ_COMPRESS and LZ_UNCOMPRESS procedures to perform the actions. It converts a 32K string made up of the numbers 0-9 into a BLOB, compresses the blob, then uncompresses it.

set serveroutput on
declare
  l_original_blob      blob;
  l_compressed_blob    blob;
  l_uncompressed_blob  blob;
begin
  -- Initialize BLOBs to something.
  l_original_blob     := to_blob(utl_raw.cast_to_raw(rpad('0', 32767, '1234567890')));
  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;
/
Original Length    : 32767
Compressed Length  : 108
Uncompressed Length: 32767

PL/SQL procedure successfully completed.

SQL>

Functions

This is a repeat of the previous example, but using the LZ_COMPRESS and LZ_UNCOMPRESS functions to perform the actions.

set serveroutput on
declare
  l_original_blob      blob;
  l_compressed_blob    blob;
  l_uncompressed_blob  blob;
begin
  -- Initialize BLOBs to something.
  l_original_blob     := to_blob(utl_raw.cast_to_raw(rpad('0', 32767, '1234567890')));
  l_compressed_blob   := to_blob('1');
  l_uncompressed_blob := to_blob('1');
  
  -- Compress the data.
  l_compressed_blob := utl_compress.lz_compress (src => l_original_blob);
  
  -- Uncompress the data.
  l_uncompressed_blob := utl_compress.lz_uncompress (src => l_compressed_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;
/
Original Length    : 32767
Compressed Length  : 108
Uncompressed Length: 32767

PL/SQL procedure successfully completed.

SQL>

Compression Quality

The LZ_COMPRESS procedure and function have a QUALITY parameter that determines the quality of compression used. The parameter is an integer value from 1 to 9, where 1 is the fastest compression and 9 is the best compression. The default value is 6. The example below compares the compression of the default compression with the qualities 1 and 9.

set serveroutput on
declare
  l_original_blob      blob;
  l_compressed_blob    blob;
  l_compressed_blob_1  blob;
  l_compressed_blob_9  blob;
begin
  -- Initialize BLOBs to something.
  l_original_blob     := to_blob(utl_raw.cast_to_raw(rpad('0', 32767, '1234567890')));
  l_compressed_blob   := to_blob('1');
  l_compressed_blob_1 := to_blob('1');
  l_compressed_blob_9 := to_blob('1');
  
  -- Compress the data.
  utl_compress.lz_compress (src     => l_original_blob,
                            dst     => l_compressed_blob);
  
  utl_compress.lz_compress (src     => l_original_blob,
                            dst     => l_compressed_blob_1,
                            quality => 1);
  
  utl_compress.lz_compress (src     => l_original_blob,
                            dst     => l_compressed_blob_9,
                            quality => 9);
  
  
  -- 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('Compressed (1) Length  : ' || length(l_compressed_blob_1));
  dbms_output.put_line('Compressed (9) Length  : ' || length(l_compressed_blob_9));
  
  -- Free temporary BLOBs.             
  dbms_lob.freetemporary(l_original_blob);
  dbms_lob.freetemporary(l_compressed_blob);
  dbms_lob.freetemporary(l_compressed_blob_1);
  dbms_lob.freetemporary(l_compressed_blob_9);
end;
/
original length        : 32767
compressed length      : 108
compressed (1) length  : 222
compressed (9) length  : 108

PL/SQL procedure successfully completed.

SQL>

We can see for this data, the default compression gives the same result as the value 9, both of which out perform quality level 1.

Miscellaneous

Here are some general points about using the UTL_COMPRESS package.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.