Oracle | UTL_SYS

Oracle UTL_SYS_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 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	LZ_COMPRESS_CLOSE	LZ_UNCOMPRESS_BLOB
LZ_COMPRESS	LZ_COMPRESS_OPEN	LZ_UNCOMPRESS_CLOSE
LZ_COMPRESS_ADD	LZ_UNCOMPRESS	LZ_UNCOMPRESS_EXTRACT
LZ_COMPRESS_BFILE	LZ_UNCOMPRESS_BFILE	LZ_UNCOMPRESS_OPEN
LZ_COMPRESS_BLOB

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