Compress / decompress Oracle data package-------------------------------------------------------------------------------- Name: GZIP - Compress / decompress oracle data packageOracle version: 8.1.5 and higherDescription: Implements pl/sql pack and unpack methods using JDK java.util.zip package for both RAW and BLOB Oracle typesMethods: FUNCTION pack(buf IN raw) RETURN raw; FUNCTION unpack(buf IN raw) RETURN raw; PROCEDURE packBlob(src IN blob, dst IN OUT blob); PROCEDURE unpackBlob(src IN blob, dst IN OUT blob); Installation steps: Create java class to implement gzip methods: CREATE OR REPLACE JAVA SOURCE NAMED "GZIPImpl" AS import java.io.*; import java.util.zip.*; import java.sql.*; import oracle.sql.*;public class GZIPImpl { public static byte[] packRaw(byte[] b) { ByteArrayOutputStream outBuffer = new ByteArrayOutputStream(); try { GZIPOutputStream gzip = new GZIPOutputStream(outBuffer); gzip.write(b); gzip.close(); } catch (IOException e) { System.err.println(e); } return outBuffer.toByteArray(); } public static byte[] unpackRaw(byte[] b) { ByteArrayOutputStream outBuffer = new ByteArrayOutputStream(); ByteArrayInputStream inBuffer = new ByteArrayInputStream(b); try { GZIPInputStream gzip = new GZIPInputStream(inBuffer); byte[] tmpBuffer = new byte[256]; int n; while ((n = gzip.read(tmpBuffer)) >= 0) outBuffer.write(tmpBuffer, 0, n); } catch (IOException e) { System.err.println(e); } return outBuffer.toByteArray(); } public static void packBlob(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob[]) { try { OutputStream outBuffer = dstBlob[0].getBinaryOutputStream(); InputStream inBuffer = srcBlob.getBinaryStream(); GZIPOutputStream gzip = new GZIPOutputStream(outBuffer); byte[] tmpBuffer = new byte[256]; int n; while ((n = inBuffer.read(tmpBuffer)) >= 0) gzip.write(tmpBuffer, 0, n); gzip.close(); } catch (SQLException e) { System.err.println(e); } catch (IOException e) { System.err.println(e); } } public static void unpackBlob(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob[]) { try { OutputStream outBuffer = dstBlob[0].getBinaryOutputStream(); InputStream inBuffer = srcBlob.getBinaryStream(); GZIPInputStream gzip = new GZIPInputStream(inBuffer); byte[] tmpBuffer = new byte[256]; int n; while ((n = gzip.read(tmpBuffer)) >= 0) outBuffer.write(tmpBuffer, 0, n); outBuffer.close(); } catch (SQLException e) { System.err.println(e); } catch (IOException e) { System.err.println(e); } }}; /
Compile java class and check for errors: alter java source "GZIPImpl" compile / show errorsCreate a wrapper PL/SQL package: CREATE PACKAGE GZIP AS FUNCTION pack(buf IN raw) RETURN raw; FUNCTION unpack(buf IN raw) RETURN raw; PROCEDURE packBlob(src IN blob, dst IN OUT blob); PROCEDURE unpackBlob(src IN blob, dst IN OUT blob); END; /CREATE PACKAGE BODY GZIP AS FUNCTION pack(buf IN raw) RETURN raw AS LANGUAGE JAVA NAME 'GZIPImpl.packRaw(byte[]) return byte[]'; FUNCTION unpack(buf IN raw) RETURN raw AS LANGUAGE JAVA NAME 'GZIPImpl.unpackRaw(byte[]) return byte[]'; PROCEDURE packBlob(src IN blob, dst IN OUT blob) AS LANGUAGE JAVA NAME 'GZIPImpl.packBlob(oracle.sql.BLOB, oracle.sql.BLOB[])'; PROCEDURE unpackBlob(src IN blob, dst IN OUT blob) AS LANGUAGE JAVA NAME 'GZIPImpl.unpackBlob(oracle.sql.BLOB, oracle.sql.BLOB[])'; END; /
Test GZIP package: DECLARE src varchar2(32767); srcRaw raw(32767); zip raw(32767); unzip varchar2(32767); tmp varchar2(250); tmpRaw raw(250); srcBlob BLOB; zipBlob BLOB; unzipBlob BLOB; read_amount binary_integer; BEGIN src := 'Many sources of information contain redundant data or data that adds '|| 'little to the stored information. This results in tremendous amounts '|| 'of data being transferred between client and server applications or '|| 'computers in general. The obvious solution to the problems of data '|| 'storage and information transfer is to install additional storage '|| 'devices and expand existing communication facilities. To do so, however, '|| 'requires an increase in an organization''s operating costs. One method '|| 'to alleviate a portion of data storage and information transfer is '|| 'through the representation of data by more efficient code. This article '|| 'shows how to compress and decompress data, efficiently and conveniently, '|| 'from within your ORACLE applications using the GZIP package.'; DBMS_OUTPUT.put_line('1. RAW TEST'); DBMS_OUTPUT.put_line('==========='); DBMS_OUTPUT.put_line('1.1 SOURCE TEXT:'); DBMS_OUTPUT.put_line(' Cut = '||substr(src, 1, 60)||' ...'); DBMS_OUTPUT.put_line(' Length = '||length(src)); DBMS_OUTPUT.put_line('1.2 COMPRESSED RAW:'); srcRaw := UTL_RAW.cast_to_raw(src); -- Don't forget explicit convertion! zip := GZIP.pack(srcRaw); tmp := ''; for i in 1 .. 15 loop tmp := tmp||'0x'||UTL_RAW.substr(zip, i, 1)||' '; end loop; DBMS_OUTPUT.put_line(' Cut = '||tmp||' ...'); DBMS_OUTPUT.put_line(' Length = '||UTL_RAW.length(zip)); DBMS_OUTPUT.put_line('1.3 DECOMPRESSED TEXT:'); unzip := UTL_RAW.cast_to_varchar2(GZIP.unpack(zip)); -- Decompress and convert to varchar2 DBMS_OUTPUT.put_line(' Cut = '||substr(unzip, 1, 60)||' ...'); DBMS_OUTPUT.put_line(' Length = '||length(unzip)); DBMS_OUTPUT.put_line('2. BLOB TEST'); DBMS_OUTPUT.put_line('============'); -- Prepare temporary Blobs DBMS_LOB.CreateTemporary(srcBlob, TRUE); DBMS_LOB.CreateTemporary(zipBlob, TRUE); DBMS_LOB.CreateTemporary(unzipBlob, TRUE); DBMS_OUTPUT.put_line('2.1 SOURCE BLOB:'); srcRaw := UTL_RAW.cast_to_raw(src); -- Don't forget explicit convertion! DBMS_LOB.write(srcBlob, UTL_RAW.length(srcRaw), 1, srcRaw); -- Fill source blob -- Print results read_amount := 60; DBMS_LOB.read(srcBlob, read_amount, 1, tmpRaw); DBMS_OUTPUT.put_line(' Cut = '||UTL_RAW.cast_to_varchar2(tmpRaw)||' ...'); DBMS_OUTPUT.put_line(' Length = '||to_char(DBMS_LOB.GetLength(srcBlob))); DBMS_OUTPUT.put_line('2.2 COMPRESSED BLOB:'); GZIP.packBlob(srcBlob, zipBlob); -- Compress srcBlob into zipBlob -- Print results read_amount := 60; DBMS_LOB.read(zipBlob, read_amount, 1, tmpRaw); tmp := ''; for i in 1 .. 15 loop tmp := tmp||'0x'||UTL_RAW.substr(tmpRaw, i, 1)||' '; end loop; DBMS_OUTPUT.put_line(' Cut = '||tmp||' ...'); DBMS_OUTPUT.put_line(' Length = '||to_char(DBMS_LOB.GetLength(zipBlob))); DBMS_OUTPUT.put_line('2.3 DECOMPRESSED BLOB:'); GZIP.unpackBlob(zipBlob, unzipBlob); -- Decompress zipBlob into unzipBlob -- Print results read_amount := 60; DBMS_LOB.read(unzipBlob, read_amount, 1, tmpRaw); DBMS_OUTPUT.put_line(' Cut = '||UTL_RAW.cast_to_varchar2(tmpRaw)||' ...'); DBMS_OUTPUT.put_line(' Length = '||to_char(DBMS_LOB.GetLength(unzipBlob))); -- Release temporary blobs DBMS_LOB.FreeTemporary(srcBlob); DBMS_LOB.FreeTemporary(zipBlob); DBMS_LOB.FreeTemporary(unzipBlob); END; /Code above should produce next results:1. RAW TEST =========== 1.1 SOURCE TEXT: Cut = Many sources of information contain redundant data or data t ... Length = 754 1.2 COMPRESSED RAW: Cut = 0x1F 0x8B 0x08 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x95 0x92 0xCB 0x8E 0x14 ... Length = 408 1.3 DECOMPRESSED TEXT: Cut = Many sources of information contain redundant data or data t ... Length = 754 2. BLOB TEST ============ 2.1 SOURCE BLOB: Cut = Many sources of information contain redundant data or data t ... Length = 754 2.2 COMPRESSED BLOB: Cut = 0x1F 0x8B 0x08 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x95 0x92 0xCB 0x8E 0x14 ... Length = 408 2.3 DECOMPRESSED BLOB: Cut = Many sources of information contain redundant data or data t ... Length = 754 Examples Compress/decompress on fly stored data using DML (less when 4000 bytes in size): insert into my_table (compressed_text) values (GZIP.pack(UTL_RAW.cast_to_raw('1234567890'))); select UTL_RAW.cast_to_varchar2(GZIP.unpack(compressed_text)) from my_table;Compress/decompress BLOB stored data (size <= 4Gb):DECLARE SrcBlobLocator BLOB; DstBlobLocator BLOB; BEGIN select blob_data into SrcBlobLocator from src_table where id=1; select blob_data into DstBlobLocator from dst_table where id=1; GZIP.packBlob(SrcBlobLocator, DstBlobLocator); commit; END;
Compress / decompress Oracle data package--------------------------------------------------------------------------------
Name: GZIP - Compress / decompress oracle data packageOracle version: 8.1.5 and higherDescription: Implements pl/sql pack and unpack methods using JDK java.util.zip package for both RAW and BLOB Oracle typesMethods:
FUNCTION pack(buf IN raw) RETURN raw;
FUNCTION unpack(buf IN raw) RETURN raw;
PROCEDURE packBlob(src IN blob, dst IN OUT blob);
PROCEDURE unpackBlob(src IN blob, dst IN OUT blob);
Installation steps:
Create java class to implement gzip methods:
CREATE OR REPLACE JAVA SOURCE NAMED "GZIPImpl"
AS
import java.io.*;
import java.util.zip.*;
import java.sql.*;
import oracle.sql.*;public class GZIPImpl
{
public static byte[] packRaw(byte[] b) {
ByteArrayOutputStream outBuffer = new ByteArrayOutputStream();
try {
GZIPOutputStream gzip = new GZIPOutputStream(outBuffer);
gzip.write(b);
gzip.close();
}
catch (IOException e) {
System.err.println(e);
}
return outBuffer.toByteArray();
} public static byte[] unpackRaw(byte[] b) {
ByteArrayOutputStream outBuffer = new ByteArrayOutputStream();
ByteArrayInputStream inBuffer = new ByteArrayInputStream(b);
try {
GZIPInputStream gzip = new GZIPInputStream(inBuffer);
byte[] tmpBuffer = new byte[256];
int n;
while ((n = gzip.read(tmpBuffer)) >= 0)
outBuffer.write(tmpBuffer, 0, n);
}
catch (IOException e) {
System.err.println(e);
}
return outBuffer.toByteArray();
} public static void packBlob(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob[]) {
try {
OutputStream outBuffer = dstBlob[0].getBinaryOutputStream();
InputStream inBuffer = srcBlob.getBinaryStream();
GZIPOutputStream gzip = new GZIPOutputStream(outBuffer);
byte[] tmpBuffer = new byte[256];
int n;
while ((n = inBuffer.read(tmpBuffer)) >= 0)
gzip.write(tmpBuffer, 0, n);
gzip.close();
}
catch (SQLException e) {
System.err.println(e);
}
catch (IOException e) {
System.err.println(e);
}
} public static void unpackBlob(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob[]) {
try {
OutputStream outBuffer = dstBlob[0].getBinaryOutputStream();
InputStream inBuffer = srcBlob.getBinaryStream();
GZIPInputStream gzip = new GZIPInputStream(inBuffer);
byte[] tmpBuffer = new byte[256];
int n;
while ((n = gzip.read(tmpBuffer)) >= 0)
outBuffer.write(tmpBuffer, 0, n);
outBuffer.close();
}
catch (SQLException e) {
System.err.println(e);
}
catch (IOException e) {
System.err.println(e);
}
}};
/
Compile java class and check for errors:
alter java source "GZIPImpl" compile
/
show errorsCreate a wrapper PL/SQL package:
CREATE PACKAGE GZIP AS
FUNCTION pack(buf IN raw) RETURN raw;
FUNCTION unpack(buf IN raw) RETURN raw;
PROCEDURE packBlob(src IN blob, dst IN OUT blob);
PROCEDURE unpackBlob(src IN blob, dst IN OUT blob);
END;
/CREATE PACKAGE BODY GZIP AS
FUNCTION pack(buf IN raw) RETURN raw
AS LANGUAGE JAVA
NAME 'GZIPImpl.packRaw(byte[]) return byte[]'; FUNCTION unpack(buf IN raw) RETURN raw
AS LANGUAGE JAVA
NAME 'GZIPImpl.unpackRaw(byte[]) return byte[]'; PROCEDURE packBlob(src IN blob, dst IN OUT blob)
AS LANGUAGE JAVA
NAME 'GZIPImpl.packBlob(oracle.sql.BLOB, oracle.sql.BLOB[])'; PROCEDURE unpackBlob(src IN blob, dst IN OUT blob)
AS LANGUAGE JAVA
NAME 'GZIPImpl.unpackBlob(oracle.sql.BLOB, oracle.sql.BLOB[])';
END;
/
Test GZIP package:
DECLARE
src varchar2(32767);
srcRaw raw(32767);
zip raw(32767);
unzip varchar2(32767);
tmp varchar2(250);
tmpRaw raw(250);
srcBlob BLOB;
zipBlob BLOB;
unzipBlob BLOB;
read_amount binary_integer;
BEGIN
src := 'Many sources of information contain redundant data or data that adds '||
'little to the stored information. This results in tremendous amounts '||
'of data being transferred between client and server applications or '||
'computers in general. The obvious solution to the problems of data '||
'storage and information transfer is to install additional storage '||
'devices and expand existing communication facilities. To do so, however, '||
'requires an increase in an organization''s operating costs. One method '||
'to alleviate a portion of data storage and information transfer is '||
'through the representation of data by more efficient code. This article '||
'shows how to compress and decompress data, efficiently and conveniently, '||
'from within your ORACLE applications using the GZIP package.'; DBMS_OUTPUT.put_line('1. RAW TEST');
DBMS_OUTPUT.put_line('==========='); DBMS_OUTPUT.put_line('1.1 SOURCE TEXT:');
DBMS_OUTPUT.put_line(' Cut = '||substr(src, 1, 60)||' ...');
DBMS_OUTPUT.put_line(' Length = '||length(src)); DBMS_OUTPUT.put_line('1.2 COMPRESSED RAW:');
srcRaw := UTL_RAW.cast_to_raw(src); -- Don't forget explicit convertion!
zip := GZIP.pack(srcRaw);
tmp := '';
for i in 1 .. 15 loop
tmp := tmp||'0x'||UTL_RAW.substr(zip, i, 1)||' ';
end loop;
DBMS_OUTPUT.put_line(' Cut = '||tmp||' ...');
DBMS_OUTPUT.put_line(' Length = '||UTL_RAW.length(zip)); DBMS_OUTPUT.put_line('1.3 DECOMPRESSED TEXT:');
unzip := UTL_RAW.cast_to_varchar2(GZIP.unpack(zip)); -- Decompress and convert to varchar2
DBMS_OUTPUT.put_line(' Cut = '||substr(unzip, 1, 60)||' ...');
DBMS_OUTPUT.put_line(' Length = '||length(unzip)); DBMS_OUTPUT.put_line('2. BLOB TEST');
DBMS_OUTPUT.put_line('============'); -- Prepare temporary Blobs
DBMS_LOB.CreateTemporary(srcBlob, TRUE);
DBMS_LOB.CreateTemporary(zipBlob, TRUE);
DBMS_LOB.CreateTemporary(unzipBlob, TRUE); DBMS_OUTPUT.put_line('2.1 SOURCE BLOB:');
srcRaw := UTL_RAW.cast_to_raw(src); -- Don't forget explicit convertion!
DBMS_LOB.write(srcBlob, UTL_RAW.length(srcRaw), 1, srcRaw); -- Fill source blob
-- Print results
read_amount := 60;
DBMS_LOB.read(srcBlob, read_amount, 1, tmpRaw);
DBMS_OUTPUT.put_line(' Cut = '||UTL_RAW.cast_to_varchar2(tmpRaw)||' ...');
DBMS_OUTPUT.put_line(' Length = '||to_char(DBMS_LOB.GetLength(srcBlob))); DBMS_OUTPUT.put_line('2.2 COMPRESSED BLOB:');
GZIP.packBlob(srcBlob, zipBlob); -- Compress srcBlob into zipBlob
-- Print results
read_amount := 60;
DBMS_LOB.read(zipBlob, read_amount, 1, tmpRaw);
tmp := '';
for i in 1 .. 15 loop
tmp := tmp||'0x'||UTL_RAW.substr(tmpRaw, i, 1)||' ';
end loop;
DBMS_OUTPUT.put_line(' Cut = '||tmp||' ...');
DBMS_OUTPUT.put_line(' Length = '||to_char(DBMS_LOB.GetLength(zipBlob))); DBMS_OUTPUT.put_line('2.3 DECOMPRESSED BLOB:');
GZIP.unpackBlob(zipBlob, unzipBlob); -- Decompress zipBlob into unzipBlob
-- Print results
read_amount := 60;
DBMS_LOB.read(unzipBlob, read_amount, 1, tmpRaw);
DBMS_OUTPUT.put_line(' Cut = '||UTL_RAW.cast_to_varchar2(tmpRaw)||' ...');
DBMS_OUTPUT.put_line(' Length = '||to_char(DBMS_LOB.GetLength(unzipBlob))); -- Release temporary blobs
DBMS_LOB.FreeTemporary(srcBlob);
DBMS_LOB.FreeTemporary(zipBlob);
DBMS_LOB.FreeTemporary(unzipBlob);
END;
/Code above should produce next results:1. RAW TEST
===========
1.1 SOURCE TEXT:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
1.2 COMPRESSED RAW:
Cut = 0x1F 0x8B 0x08 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x95 0x92 0xCB 0x8E 0x14 ...
Length = 408
1.3 DECOMPRESSED TEXT:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
2. BLOB TEST
============
2.1 SOURCE BLOB:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
2.2 COMPRESSED BLOB:
Cut = 0x1F 0x8B 0x08 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x95 0x92 0xCB 0x8E 0x14 ...
Length = 408
2.3 DECOMPRESSED BLOB:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
Examples
Compress/decompress on fly stored data using DML (less when 4000 bytes in size): insert into my_table (compressed_text) values (GZIP.pack(UTL_RAW.cast_to_raw('1234567890')));
select UTL_RAW.cast_to_varchar2(GZIP.unpack(compressed_text)) from my_table;Compress/decompress BLOB stored data (size <= 4Gb):DECLARE
SrcBlobLocator BLOB;
DstBlobLocator BLOB;
BEGIN
select blob_data into SrcBlobLocator from src_table where id=1;
select blob_data into DstBlobLocator from dst_table where id=1;
GZIP.packBlob(SrcBlobLocator, DstBlobLocator);
commit;
END;