Compressing binary large objects (BLOB) or files using PL/SQL has been challenging. For at least a decade, Oracle includes UTL_COMPRESS as a means of compressing or zipping files. This technology faced a few limitations specifically, adding a BLOB with the UTL_COMPRESS.LZ_COMPRESS_ADD procedure. The source “file” is required to be raw. The utility spews errors when you asked it compress a BLOB directly. PLSQL Compress BLOB is easier with APEX_ZIP.
There are alternatives. Anton Scheffer wrote and maintains a fine package called AS_ZIP (the “AS” likely to reference Anton’s initials.
I learned this week after searching and searching that Oracle has picked up Anton’s utility and included it as an APEX Package. It is called APEX_ZIP. According to Christian Neumueller, it was Patrick Wolff that adopted Anton’s work.
How did I miss this? Well, I searched compress BLOB. I ought to have searched ZIP file. As of early May 2017, the phrases on the APEX_ZIP utility documentation are zip and unzip. The phrases on the UTL_COMPRESS utility are compress and uncompress. I avoided searching “ZIP” because of its association with a brand. Dooh! Let’s hope that this blog helps others find the compress blob utility APEX_ZIP.
PLSQL BLOB Compression
The purpose of this blog is to assist the next Oracle PLSQL developer who wants to compress blobs or zip blobs to find the APEX_ZIP utility. Have I used the phrases compress blob and zip blob enough to be picked up by search engines? I hope so.
APEX_ZIP
Please search for the APEX_ZIP utility. There are a few nuggets to know. First, you really can add a file to an empty set. Unlike Alice’s experience at a tea party, when offered “a little more tea?”. She states: “Well, I haven’t had any yet, so I can’t very well take more.” And the readers of all things Oracle PL/SQL know that one can’t add to a null. But, in this case you may add to an empty set.
ORA-06502
Protect the procedure call and screen for nulls. A null filename gives a ORA-06502 “numeric or value error” from sys.utl_raw.
What’s Next
Having a big ole BLOB with a set of zipped or compressed BLOBs doesn’t yet help the end user. We tend to use global temporary tables (“GTT”) as a means of hosting BLOBs for users to download via Oracle APEX. While GTT are self-cleaning and offer protection against nosy neighbors from seeing data, when using Global Temporary Tables in APEX, they can be seen by another user. So we always post the session ID with a record and we filter on that. That keeps nosy neighbors out and boundaries clean.
Function ZIP_BLOBS
The function below is a sample. It spins through a table with a bunch of blobs that need compressing. It puts the compressed BLOB into a global temporary table that is easily used in Oracle APEX.
Contributors
Anton Scheffer wrote a fine blog and utility in 2010. He has updated this utility as recently as April 2016. His code gives insights into the workings. Dimitri Gielis unceremoniously pointed out that had I searched ZIP instead of compress, I would have save days of misery. We really do need friends like that! Christian Neumueller shared the story of APEX_ZIP package and confirmed its roots. Patrick Wolf adopted Anton’s work.
Sample Code/Function
— sql create or replace function zip_blobs return number
as l_compress_blob blob; l_session number; l_zip_filename varchar2(60); l_sb_pk number; begin
l_zip_filename := ‘compressed_blobs.zip’; dbms_lob.createtemporary(l_compress_blob, false); l_session := apex_application.g_instance;
delete from tg_store_blob where sb_session = l_session;
– fetch blobs, build zip file for i in ( select doc_pk, doc_blob, doc_filename from tg_doc ) loop – Compress BLOB / ZIP BLOB if i.doc_filename is not null and i.doc_blob is not null then apex_zip.add_file ( p_zipped_blob => l_compress_blob, p_file_name => i.doc_filename, p_content => i.doc_blob); end if; – parameters not null end loop;
– Let the utility close up the zip file and add necessary footers apex_zip.finish(l_compress_blob);
insert into tg_store_blob ( sb_session, sess_date, blob_file,
created_date,
blob_mimetype,
blob_filename
) values (
l_session,
localtimestamp,
l_compress_blob,
localtimestamp,
null,
l_zip_filename
) returning sb_pk into l_sb_pk; commit; return l_sb_pk; end zip_blobs;
