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

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.

Procedures
Functions
Compression Quality
Miscellaneous

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.

The level of compression possible depends on the source data. In these examples we've used highly compressible data, so the results are very good. For other source data the level of compression may be very poor.
The procedures and functions use temporary LOBs. These temporary LOBs must be freed using the DBMS_LOB.FREETEMPORARY procedure.
The effectiveness of the quality parameter on the level of compression will vary depending on the size and nature of the source data. For some compressions there will be no difference between quality 1 and 9. For others the difference may be considerable.
There are overloads of the LZ_COMPRESS and LZ_UNCOMPRESS procedures and functions to support RAW, BLOB and BFILE data. A RAW input type returns a RAW output type. A BLOB input type returns a BLOB output type. A BFILE input type returns a BLOB output.
A source BFILE must be opened with the DBMS_LOB.FILEOPEN procedure prior to being used by UTL_COMPRESS package.
The UTL_COMPRESS package supports piecewise operations in addition to these single-call operations.
If data is already compressed, attempting to recompress it may result in the final size being the same or increasing.
The compressed data is compatible with gzip (with the -n or --no-name option)/gunzip on a single file.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.