General Information
Library Note
Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com . Request a Workshop for
your organization today.
Purpose
Internal support for the supported UTL_COMPRESS package
AUTHID
DEFINER
Demo Table and Data
Use the TEST table COMPRESS_DEMO procedure from the UTL_COMPRESS page in the library
Dependencies
DBMS_LOB
UTL_COMPRESS
UTL_SYS_CMP_LIB
Documented
No
First Available
12.1.0
Security Model
Owned by SYS with no privileges granted
Source
$ORACLE_HOME/rdbms/admin/prvtcmpl.plb
Subprograms
ISOPEN
Checks to see if the handle to a piecewise operation context is open or closed
utl_sys_compress.isopen(handle IN BINARY_INTEGER)
RETURN BOOLEAN;
See LZ_UNCOMPRESS_EXTRACT Demo Below
LZ_COMPRESS
With an uncompressed BLOB as an input output a compressed BLOB
Overload 1
utl_sys_compress.lz_compress(
src IN BLOB,
quality IN BINARY_INTEGER)
RETURN BLOB;
CREATE TABLE test (
fname VARCHAR2(30),
iblob BLOB);
Note: The directory CTEMP's creation can be found on the library page for directories (link below).
CREATE OR REPLACE PROCEDURE compress_demo(v_fname VARCHAR2, vQual BINARY_INTEGER) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
i NUMBER;
BEGIN
src_file := bfilename('CTEMP', v_fname);
i := dbms_utility.get_time;
-- insert a NULL record to lock
INSERT INTO test
(fname, iblob)
VALUES
('Uncompressed', EMPTY_BLOB())
RETURNING iblob INTO dst_file;
-- lock record
SELECT iblob
INTO dst_file
FROM test
WHERE fname = 'Uncompressed'
FOR UPDATE;
-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
-- determine length
lgh_file := dbms_lob.getlength(src_file);
-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
-- update the blob field
UPDATE test
SET iblob = dst_file
WHERE fname = 'Uncompressed';
-- close file
dbms_lob.fileclose(src_file);
dbms_output.put_line(dbms_utility.get_time - i);
--=====================================================
i := dbms_utility.get_time;
-- insert a NULL record to lock
INSERT INTO test
(fname, iblob)
VALUES
('Compressed', EMPTY_BLOB())
RETURNING iblob INTO dst_file;
-- lock record
SELECT iblob
INTO dst_file
FROM test
WHERE fname = 'Compressed'
FOR UPDATE;
-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
-- determine length
lgh_file := dbms_lob.getlength(src_file);
-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
-- update the blob field
UPDATE test
SET iblob = utl_sys_compress.lz_compress (dst_file, vQual)
WHERE fname = 'Compressed';
COMMIT;
-- close file
dbms_lob.fileclose(src_file);
dbms_output.put_line(dbms_utility.get_time - i);
END compress_demo;
/
set serveroutput on
exec compress_demo('image1.dcm', 3);
SELECT fname, dbms_lob.getlength(iblob)
FROM test;
With a BFILE as an input output a compressed BLOB
Overload 2
utl_sys_compress.lz_compress(
src IN BFILE,
quality IN BINARY_INTEGER)
RETURN BLOB;
TBD
LZ_COMPRESS_ADD
Adds a piece of compressed data
utl_sys_compress.lz_compress_add(
handle IN BINARY_INTEGER,
dst IN OUT BLOB,
src IN RAW);
TBD
LZ_COMPRESS_BFILE
With a BFILE as an input output a compressed BLOB
utl_sys_compress.lz_compress_bfile(
src IN BFILE,
dst IN OUT BLOB,
quality IN BINARY_INTEGER);
TBD
LZ_COMPRESS_BLOB
With an uncompressed BLOB as an input output a compressed BLOB
utl_sys_compress.lz_compress_blob(
src IN BLOB,
dst IN OUT BLOB,
quality IN BINARY_INTEGER);
TBD
LZ_COMPRESS_CLOSE
Closes and finishes a piecewise compress operation
utl_sys_compress.lz_compress_close(
handle IN BINARY_INTEGER,
dst IN OUT BLOB);
TBD
LZ_COMPRESS_OPEN
Initializes a piecewise context that maintains the compress state and data
utl_sys_compress.lz_compress_open(
dst IN OUT BLOB,
quality IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
TBD
LZ_UNCOMPRESS
Uncompress BLOB to BLOB
Overload 1
utl_sys_compress.lz_uncompress(src IN BLOB)
RETURN BLOB;
set serveroutput on
DECLARE
i NUMBER;
b BLOB;
BEGIN
i := dbms_utility.get_time;
SELECT iblob
INTO b
FROM test
WHERE fname = 'Uncompressed';
dbms_output.put_line('Uncompressed: ' ||
TO_CHAR(dbms_utility.get_time - i));
i := dbms_utility.get_time;
SELECT utl_sys_compress.lz_uncompress (iblob)
INTO b
FROM test
WHERE fname = 'Compressed';
dbms_output.put_line('Uncompress: ' || TO_CHAR(dbms_utility.get_time - i));
END;
/
Uncompress BFILE to BLOB
Overload 2
utl_sys_compress.lz_uncompress(src IN BFILE)
RETURN BLOB;
TBD
LZ_UNCOMPRESS_ BFILE
Uncompress BFILE to BLOB
utl_sys_compress.lz_uncompress_bfile(
src IN BFILE,
dst IN OUT BLOB);
TBD
LZ_UNCOMPRESS_BLOB
Uncompress BLOB to BLOB
utl_sys_compress.lz_uncompress_blob(
src IN BLOB,
dst IN OUT BLOB);
TBD
LZ_UNCOMPRESS_CLOSE
Closes and finishes a piecewise uncompress
utl_sys_compress.lz_uncompress_close(handle IN BINARY_INTEGER);
See LZ_UNCOMPRESS_EXTRACT Demo Below
LZ_UNCOMPRESS_EXTRACT
Used by UTL_COMPRESS to extract a piece of uncompressed data which requires conversion to RAW
Produces an ORA-29294 if used on an uncompressed BLOB
utl_sys_compress.lz_uncompress_extract(
dst IN OUT RAW,
handle IN BINARY_INTEGER);
set serveroutput on
DECLARE
bob BLOB;
rob RAW(32767);
handle BINARY_INTEGER;
amount INTEGER := 32766/2;
offset INTEGER := 1;
BEGIN
SELECT iblob
INTO bob
FROM test
WHERE fname = 'Compressed';
SELECT utl_sys_compress.lz_uncompress_open (bob)
INTO handle
FROM dual;
IF utl_sys_compress.isopen (handle) THEN
dbms_output.put_line(handle);
rob := dbms_lob.substr(bob, amount, offset);
dbms_output.put_line('Unextracted: ' || length(rob));
utl_sys_compress.lz_uncompress_extract (rob, handle);
dbms_output.put_line('Extracted: ' || length(rob));
utl_sys_compress.lz_uncompress_close (handle);
END IF;
END;
/
LZ_UNCOMPRESS_OPEN
Initializes a piecewise context that maintain the uncompress state and data
utl_sys_compress.lz_uncompress_open(src IN BLOB)
RETURN BINARY_INTEGER;
See LZ_UNCOMPRESS_EXTRACT Demo Above