Oracle UTL_COMPRESS
Version 21c

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 Performs Lempel-Ziv compression of RAW and BLOB data

It is the caller's responsibility to free the temporary LOB returned by the LZ* functions with DBMS_LOB.FREETEMPORARY call.
AUTHID DEFINER
Constants
Name Data Type Value
UTLCOMP_MAX_HANDLE PLS_INTEGER 5
Dependencies
DBMS_AUTO_REPORT_INTERNAL UTL_CMP_LIB UTL_SYS_COMPRESS
Documented Yes
Exceptions
Error Code Reason
ORA-29261 INVALID_ARGUMENT: Argument(s) of an invalid type or value
ORA-29293 STREAM_ERROR: Error during compression or uncompression
ORA-29294 DATA_ERROR: Invalidly formatted input or output stream
ORA-29297 BUFFER_TOO_SMALL: Compressed representation is too large
ORA-29299 INVALID_HANDLE: Invalid handle for piecewise (un)compress
First Available 10.1
Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/utlcomp.sql
Subprograms
ISOPEN LZ_COMPRESS_CLOSE LZ_UNCOMPRESS_CLOSE
LZ_COMPRESS LZ_COMPRESS_OPEN LZ_UNCOMPRESS_EXTRACT
LZ_COMPRESS_ADD LZ_UNCOMPRESS LZ_UNCOMPRESS_OPEN
 
ISOPEN
Checks to see if the handle to a piecewise operation context is open or closed utl_compress.isopen(handle IN BINARY_INTEGER) RETURN BOOLEAN;
See LZ_COMPRESS_ADD Demo Below
 
LZ_COMPRESS
Compress BLOB

Overload 1		 utl_compress.lz_compress(
src     IN BLOB,
quality IN BINARY_INTEGER DEFAULT 6)
RETURN BLOB;
CREATE TABLE test (
fname VARCHAR2(30),
iblob BLOB);

-- 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_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

SELECT fname, dbms_lob.getlength(iblob)
FROM test;
Compress RAW

Overload 2		 utl_compress.lz_compress(
src     IN RAW,
quality IN BINARY_INTEGER DEFAULT 6)
RETURN RAW;
TBD
Compress BFILE

Overload 3		 utl_compress.lz_compress(
src     IN BFILE,
quality IN BINARY_INTEGER DEFAULT 6)
RETURN BLOB;
TBD
Compress BLOB

Overload 4		 utl_compress.lz_compress(
src     IN            BLOB,
dst     IN OUT NOCOPY BLOB,
quality IN            BINARY_INTEGER DEFAULT 6)
RETURN BLOB;
TBD
Compress BLOB

Overload 5		 utl_compress.lz_compress(
src     IN            BFILE,
dst     IN OUT NOCOPY BLOB,
quality IN            BINARY_INTEGER DEFAULT 6)
RETURN BLOB;
TBD
 
LZ_COMPRESS_ADD
Adds a piece of compressed data utl_compress.lz_compress_add(
handle IN            BINARY_INTEGER,
dst    IN OUT NOCOPY BLOB,
source IN            RAW);
DECLARE
 b      BLOB;
 r      RAW(32);
 handle BINARY_INTEGER;
BEGIN
  SELECT iblob
  INTO b
  FROM test
  WHERE fname = 'Uncompressed'
  FOR UPDATE;

  handle := utl_compress.lz_compress_open(b);

  IF NOT utl_compress.isopen(handle) THEN
    RAISE NO_DATA_FOUND;
  END IF;

  r := utl_raw.cast_to_raw('ABC');
  utl_compress.lz_compress_add(handle, b, r);
  utl_compress.lz_compress_close(handle, b);
END;
/
 
LZ_COMPRESS_CLOSE
Closes and finishes a piecewise compress operation utl_compress.close(
handle IN            BINARY_INTEGER,
dst    IN OUT NOCOPY BLOB);
See LZ_COMPRESS_ADD Demo Below
 
LZ_COMPRESS_OPEN
Initializes a piecewise context that maintains the compress state and data utl_compress.lz_compress_open(
data_source IN BLOB,
quality     IN BINARY_INTEGER DEFAULT 6);

quality can be from 1 to 9 with 1 being fastest and 9 the slowest
See LZ_COMPRESS_ADD Demo Below
 
LZ_UNCOMPRESS
Uncompress BLOB to BLOB

Overload 1		 utl_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_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 RAW to RAW
Overload 2		 utl_compress.lz_uncompress(src IN RAW)
RETURN RAW;
TBD
Uncompress BFILE to BLOB
Overload 3		 utl_compress.lz_uncompress(src IN BFILE)
RETURN BLOB
TBD
Uncompress BLOB to BLOB
Overload 4		 utl_compress.lz_uncompress(
src IN            BLOB,
dst IN OUT NOCOPY BLOB);
TBD
Uncompress BFILE into BLOB
Overload 5		 utl_compress.lz_uncompress(
src IN            BFILE,
dst IN OUT NOCOPY BLOB)
TBD
 
LZ_UNCOMPRESS_CLOSE
Closes and finishes a piecewise uncompress utl_compress.lz_uncompress_close(handle IN BINARY_INTEGER)
See LZ_UNCOMPRESS_EXTRACT Demo Below
 
LZ_UNCOMPRESS_EXTRACT
Extracts a piece of uncompressed data utl_compress.lz_compress_uncompress_extract(
handle IN         BINARY_INTEGER,
dst    OUT NOCOPY RAW);
DECLARE
 ib       BLOB;
 ob       BLOB;
 dst_file BLOB;
 handle   BINARY_INTEGER;
BEGIN
  SELECT iblob
  INTO ib
  FROM test
  WHERE fname = 'Compressed';

  SELECT utl_compress.lz_uncompress_open(ib)
  INTO handle
  FROM dual;

  utl_compress.lz_uncompress_extract(handle, ob);

  INSERT INTO test
  (fname, iblob)
  VALUES
  ('Extracted', EMPTY_BLOB())
  RETURNING iblob INTO dst_file;

  -- lock record
  SELECT iblob
  INTO dst_file
  FROM test
  WHERE fname = 'Extracted'
  FOR UPDATE;

  UPDATE test
  SET iblob = ob
  WHERE fname = 'Extracted';
  COMMIT;

  utl_compress.lz_uncompress_close(handle);
  COMMIT;
END;
/

SELECT fname, dbms_lob.getlength(iblob)
FROM test;
 
LZ_UNCOMPRESS_OPEN
Initializes a piecewise context that maintain the uncompress state and data utl_compress.lz_uncompress_open(src IN BLOB)
RETURN BINARY_INTEGER;
See LZ_UNCOMPRESS_EXTRACT Demo Above

Related Topics
Built-in Functions
Built-in Packages
DBMS_LOB
Directories
UTL_SYS_COMPRESS
What's New In 21c
What's New In 23c

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