Zip using Oracle Stored Procedure

Right now I am using Oracle utility procedure, UTL_COMPRESS.LZ_COMPRESS(), to compress some data. But the problem is it compresses the thing using GZIP compatible format, which is not als开发者_如何学JAVAo ZIP compatible unfortunately. Therefore, the Windows XP native decompression utility can not open it (you know that compressed folder thingie). And user have to use some other utility, like 7Zip, Winzip, or Filzip etc., in order to decompress that.

So, we end up having a plan of retrieving GZIP data from Oracle, uncompress it using Java, and compress it back to ZIP (something that can be decompressed by Windows utility). It sounds ridiculous to compress-in-gzip -> decompress -> compress-again-in-zip.

Any idea how can we compress it in the desirable format in the first place, to avoid all this extra computation?

There is a Java package java.util.zip which supports the WinZip format. And in Oracle we can build java stored procedures which present Java classes in a form which can be called by native PL/SQL programs. Find out more.

So what you need to do is write out a file containing the data in its uncompressed state and then feed it through a JSP to zip it. If you don't want to write your own implementation then check out this article by Vadim Loevski. It includes a Java Stored Procedure for zipping OS files.

Note: In this context JSP means Java Stored Procedure, which is a Java program embedded in the database. It is not the same as Java Server Pages, which is a web technology, and hence the more common usage for the JSP acronym. I apologise for any confusion given.

UTL_RAW.CAST_TO_RAW is not any sort of compression algorithm. No idea where you came up with the idea that it was. RAW (and its larger cousin BLOB) are simply was of storing data that isn't a number, date or a string. You don't want to store binary data in strings because there's a chance of character conversion issues.

The correct PL/SQL package for compression is UTL_COMPRESS which uses the standard Lempel-Ziv algorithm.

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/u_compr.htm#BGBBCDDI

as_zip (blog post) is a native PL/SQL package to manipulate ZIP archives.
It handles files of up to 4 gigabytes (looks like limitation of the original ZIP format).
The package is written by Anton Scheffer and is MIT-licensed.