General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx .
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