There are times with Oracle APEX when a development team may want to provide users with the ability to host modest HTML sections. Using Markdown is a whole lot friendlier than HTML. The package will return an HTML formatted CLOB after parsing the markdown annotations.
The aim is to allow an application user to draft a quick post/message or email then let this format it to HTML.
I took advantage of the apex_util.string_to_table to help read through the CLOB. Thereby limiting the size to 32K. This allows the process to kick start without being bogged down. If we need to expand, we can.
I did not write a process for nesting lists. Given the intended use and audience, it seemed like a bridge too far. If we (collectively) need it, we can write it in.
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.
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.
Protect the procedure call and screen for nulls. A null filename gives a ORA-06502 “numeric or value error” from sys.utl_raw.
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.
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.
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.
create or replace function zip_blobs return number
l_zip_filename := 'compressed_blobs.zip';
l_session := apex_application.g_instance;
delete from tg_store_blob where sb_session = l_session;
-- fetch blobs, build zip file
for i in (
-- Compress BLOB / ZIP BLOB
if i.doc_filename is not null and i.doc_blob is not null then
p_zipped_blob => l_compress_blob,
p_file_name => i.doc_filename,
p_content => i.doc_blob);
end if; -- parameters not null
-- Let the utility close up the zip file and add necessary footers
insert into tg_store_blob (
) values (
) returning sb_pk into l_sb_pk;