CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle UTL_COMPRESS
Version 11.1
 
General Information
Note: 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.
Source {ORACLE_HOME}/rdbms/admin/utlcomp.sql
Constants

Constant

Purpose

Value

UTLCOMP_MAX_HANDLE

Define the maximum number of handles for piecewise operations 5
Dependencies
UTL_CMP_LIB UTL_SYS_COMPRESS

Exceptions
Exception Name Error Code Reason
INVALID_ARGUMENT 29261 Argument(s) of an invalid type or value
STREAM_ERROR 29293 Error during compression or uncompression
DATA_ERROR 29294 Invalidly formatted input or output stream
BUFFER_TOO_SMALL 29297 Compressed representation is too large
INVALID_HANDLE 29299 Invalid handle for piecewise (un)compress
 
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);

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_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
DBMS_LOB
UTL_SYS_COMPRESS
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [115 users online]    © 2010 psoug.org