LZ_COMPRESS needs the whole value at once. When the data comes row by row from a query, or is too big to build first, UTL_COMPRESS can compress it piece by piece: open a compressed BLOB, add each piece as it comes, and close it.
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
handle := utl_compress.lz_compress_open(dest_blob [, quality]); utl_compress.lz_compress_add(handle, dest_blob, raw_piece); utl_compress.lz_compress_close(handle, dest_blob);
Uncompressing in pieces works the same way with LZ_UNCOMPRESS_OPEN, LZ_UNCOMPRESS_EXTRACT, and LZ_UNCOMPRESS_CLOSE.
Compress Rows as They Are Read
Example:
declare
v_packed blob;
v_h binary_integer;
begin
dbms_lob.createtemporary(v_packed, true);
v_h := utl_compress.lz_compress_open(v_packed, quality => 9);
for r in (select airport_code || ',' || city || chr(10) as line
from airports order by 1) loop
utl_compress.lz_compress_add(v_h, v_packed, utl_raw.cast_to_raw(r.line));
end loop;
utl_compress.lz_compress_close(v_h, v_packed);
dbms_output.put_line('22 airports in ' || dbms_lob.getlength(v_packed) || ' bytes');
dbms_output.put_line(utl_raw.cast_to_varchar2(
dbms_lob.substr(utl_compress.lz_uncompress(v_packed), 30, 1)));
end;
/Output:
22 airports in 246 bytes AKL,Auckland AMS,Amsterdam BLR PL/SQL procedure successfully completed.
Each airport line is added as it is fetched, with quality 9 for the smallest result. The 22 airports end up in 246 bytes, and uncompressing shows the first lines back.
More Than a String Can Hold
Every flight number and status together is more text than LISTAGG or a VARCHAR2 can hold, but adding row by row has no such limit.
Example:
-- too much text for one VARCHAR2 or LISTAGG: compress it row by row
declare
v_packed blob;
v_h binary_integer;
v_bytes number := 0;
v_line raw(200);
begin
dbms_lob.createtemporary(v_packed, true);
v_h := utl_compress.lz_compress_open(v_packed);
for r in (select flight_no || ',' || status || chr(10) as line from flights) loop
v_line := utl_raw.cast_to_raw(r.line);
v_bytes := v_bytes + utl_raw.length(v_line);
utl_compress.lz_compress_add(v_h, v_packed, v_line);
end loop;
utl_compress.lz_compress_close(v_h, v_packed);
dbms_output.put_line(v_bytes || ' bytes compressed to ' || dbms_lob.getlength(v_packed));
end;
/Output:
41160 bytes compressed to 986 PL/SQL procedure successfully completed.
Over 41,000 bytes of flight data compress to under 1,000, without ever being held uncompressed in one variable.
Things to Know
- Always call LZ_COMPRESS_CLOSE: it writes the end of the gzip data, and the result cannot be uncompressed without it.
- The destination must be a temporary BLOB or a BLOB locked for update.
- Close the handle in exception handlers too, or it stays open for the session.
Related Guides
Conclusion
LZ_COMPRESS_OPEN, LZ_COMPRESS_ADD, and LZ_COMPRESS_CLOSE build a compressed BLOB piece by piece. Use them to compress query results and other streams without holding everything uncompressed, and always close the handle.
See my articles more often when you search Google.
