Oracle UTL_SYS_COMPRESS
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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

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-2014 Daniel A. Morgan All Rights Reserved