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.
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.
- 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
andLZ_UNCOMPRESS
procedures and functions to supportRAW
,BLOB
andBFILE
data. ARAW
input type returns aRAW
output type. ABLOB
input type returns aBLOB
output type. ABFILE
input type returns aBLOB
output. - A source BFILE must be opened with the
DBMS_LOB.FILEOPEN
procedure prior to being used byUTL_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...