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.
