Large text, such as logs, exports, and JSON documents, often shrinks to a fraction of its size when compressed. UTL_COMPRESS compresses RAW and BLOB values in PL/SQL with the Lempel-Ziv method in gzip format, and uncompresses them again, saving space in tables and time on the network.
Code for This Guide
The main examples are in the examples/pkg-raw-text folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
utl_compress.lz_compress(src [, quality]) -- RAW or BLOB in, same type out utl_compress.lz_uncompress(src)
QUALITY runs from 1, fastest, to 9, smallest; the default is 6.
Compress and Uncompress
The flight numbers and statuses of the first days of January are joined into text, converted to a BLOB, compressed, and uncompressed.
Example:
declare
v_text clob;
v_raw blob;
v_packed blob;
v_back blob;
d integer := 1; s integer := 1; l integer := dbms_lob.default_lang_ctx; w integer;
begin
select listagg(flight_no || ' ' || status, ', ') within group (order by flight_id)
into v_text
from flights where scheduled_departure < timestamp '2026-01-05 00:00:00 UTC';
dbms_lob.createtemporary(v_raw, true);
dbms_lob.converttoblob(v_raw, v_text, dbms_lob.lobmaxsize, d, s, 0, l, w);
v_packed := utl_compress.lz_compress(v_raw);
v_back := utl_compress.lz_uncompress(v_packed);
dbms_output.put_line('original ' || dbms_lob.getlength(v_raw) || ' bytes, compressed '
|| dbms_lob.getlength(v_packed) || ', uncompressed '
|| dbms_lob.getlength(v_back));
dbms_output.put_line('same? ' || dbms_lob.compare(v_raw, v_back)
|| ', gzip header ' || rawtohex(dbms_lob.substr(v_packed, 2, 1)));
end;
/Output:
original 2010 bytes, compressed 203, uncompressed 2010 same? 0, gzip header 1F8B PL/SQL procedure successfully completed.
2,010 bytes compress to 203, a tenth of the size, and uncompress to the same 2,010 bytes: COMPARE returns 0. The result starts with 1F8B, the gzip signature, so gzip tools can read it too.
What Compresses Well
Example:
declare
v_text clob;
v_raw blob;
v_logo blob := to_blob(bfilename('NIMBUS_FILES', 'logo.png'));
d integer := 1; s integer := 1; l integer := dbms_lob.default_lang_ctx; w integer;
begin
select listagg(flight_no || ' ' || status, ', ') within group (order by flight_id)
into v_text from flights where flight_id <= 150;
dbms_lob.createtemporary(v_raw, true);
dbms_lob.converttoblob(v_raw, v_text, dbms_lob.lobmaxsize, d, s, 0, l, w);
dbms_output.put_line('flight list: ' || dbms_lob.getlength(v_raw) || ' -> '
|| dbms_lob.getlength(utl_compress.lz_compress(v_raw)) || ' bytes');
dbms_output.put_line('PNG image: ' || dbms_lob.getlength(v_logo) || ' -> '
|| dbms_lob.getlength(utl_compress.lz_compress(v_logo)) || ' bytes');
end;
/Output:
flight list: 2250 -> 207 bytes PNG image: 117 -> 132 bytes PL/SQL procedure successfully completed.
Repetitive text shrinks to under a tenth of its size. The PNG image is already compressed, so it grows from 117 to 132 bytes, because of the gzip header. Compress text and similar data, not images, ZIP files, or encrypted data.
Things to Know
- Convert CLOB text to a BLOB with DBMS_LOB.CONVERTTOBLOB first; compression works on bytes.
- For data in tables, SecureFile compression or table compression may be simpler than compressing values yourself.
- Compress before encrypting, since encrypted data does not compress.
Related Guides
Conclusion
UTL_COMPRESS.LZ_COMPRESS and LZ_UNCOMPRESS shrink and restore RAW and BLOB values in gzip format. Use them for text-like data, choose the quality for speed or size, and skip data that is already compressed.
