“Quality is an optional compression tuning value. It allows the
UTL_COMPRESS user to choose between speed and compression quality,
meaning the percentage of reduction in size. A faster compression
speed will result in less compression of the data. A slower
compression speed will result in more compression of the data. Valid
values are [1..9], with 1=fastest and 9=slowest. The default
'quality' value is 6.”
At a setting of 1, the compressed length is 248, and at 9, the length
becomes 245. The strength or utility of using UTL_COMPRESS comes into
play when dealing with truly large objects and not just experimenting
with contrived strings. For this use case, use a document, but then, a
table will also be needed.
The setup steps are to create a table to hold the BLOB (Binary Large
Object), create a directory with read/write for the user, put a file
into the directory, initialize the record, insert a BLOB, compress it,
and compare the lengths.
set serveroutput on
create table compress_blob (indx integer, y
blob);
create directory MYDIR as 'C:\temp';
--We assume the user doing this will have
read/write on MYDIR
--Copy a file into the directory, e.g.,
A_57KB_Word_doc.doc
--This block will take care of the insert for
you, or you
--could create a separate procedure to do this
--This is simple code, does not address other
PL/SQL errors
DECLARE
ablob blob;
abfile bfile := bfilename('MYDIR',
'A_57KB_Word_doc.doc');
-- Gets a pointer to the file.
a_compressed_blob blob;
amount
integer;
asize
integer;
quality integer := 9;
cursor blob_cur is select * from
compress_blob;
BEGIN
--
-- compress_blob table is initialized with one
record because
-- the PL/SQL BLOB locator (ablob) must point to
a specific
-- EXISTING NON-NULL database BLOB.
--
-- initialize the blob locator
insert into compress_blob values (1,
empty_blob());
select y into ablob from compress_blob
where indx = 1;
-- open the bfile and get the initial
file size
dbms_lob.fileopen(abfile);
asize := dbms_lob.getlength(abfile);
dbms_output.put_line('Size of input
file: ' || asize);
-- load the file and get the size
dbms_lob.loadfromfile(ablob, abfile,
asize);
dbms_output.put_line('After
loadfromfile');
asize := dbms_lob.getlength(ablob);
dbms_output.put_line('Size of blob: '
|| asize);
-- compress the blob
-- you can experiment with varying the
quality
a_compressed_blob :=
utl_compress.lz_compress(ablob, quality);
-- insert the compressed blob
insert into compress_blob values (2,
a_compressed_blob);
-- compare the sizes of the blobs in
the table
dbms_output.put_line
('Sizes before and after
insertion/compression -->');
for c1_rec in blob_cur
loop
asize := dbms_lob.getlength(c1_rec.y);
dbms_output.put_line(asize);
end loop;
end;
/
Size of input file: 57856
After loadfromfile
Size of blob: 57856
Sizes before and after insertion/compression -->
18722
57856
PL/SQL procedure successfully completed.
When using a quality value of 1, the compressed size was 20868, so a
value of 9 represents a 10% or so improvement. As file sizes increase,
so will the amount of compression.
For production-type code, the developer will want to handle errors
such as TOO_MANY_ROWS, NO_DATA_FOUND and DUP_VAL_ON_INDEX. For
BLOB-related errors, a set of exception handlers, as shown in
documentation like what follows, could be packaged up for reuse.
EXCEPTION
when UTL_COMPRESS.INVALID_ARGUMENT
then
dbms_output.put_line('An argument was
an invalid type or value.');
when UTL_COMPRESS.BUFFER_TOO_SMALL
then
dbms_output.put_line('Compressed
representation is too big.');
when UTL_COMPRESS.DATA_ERROR then
dbms_output.put_line('Input or output
data stream has invalid format.');
when UTL_COMPRESS.STREAM_ERROR then
dbms_output.put_line('Error during
compression/uncompression of the data
stream');
when others then
dbms_output.put_line('An exception
occurred');
dbms_output.put_line(sqlcode ||
sqlerrm);
Preprocessing checks such as checking if open (then closing) and vice
versa could also be incorporated. Do not forget to use
DBMS_LOB.FREETEMPORARY to release locked items, including the source
file. For more information on
UTL_COMPRESS from the DBA’s perspective, please refer to this
utility in Chapter 6.
