Oracle UTL_SYS_COMPRESS
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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

Related Topics
Advanced Compression
Compressed Tablespaces
Hybrid Columnar Compression
Packages
SecureFile Compression
UTL_COMPRESS
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved