Oracle | UTL_SYS

Oracle UTL_SYS_COMPRESS Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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