7

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 also 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?

Adeel Ansari
  • 39,541
  • 12
  • 93
  • 133

3 Answers3

8

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.

APC
  • 144,005
  • 19
  • 170
  • 281
  • JSP is out of question, as everything is there in the database. My PL/SQL procedure is retrieving uncompressed data from one table and writing the compressed data to another table. In next step its removing all those uncompressed data, to save storage cost. – Adeel Ansari Dec 10 '10 at 07:12
  • I was thinking exactly this, I mean about Java Stored Procedure. But I have never done a single example before, that made me a little worried about the possibility. After hearing the similar thing from you, it gives me confidence. And now after coming up with a very simple kinda example, I think this is the way to go. Thank you very much for your input and for the links to valuable resources. +1 – Adeel Ansari Dec 10 '10 at 08:47
  • This is regarding your edit. "NB" means "Nota Bene", if I am not mistaken again? ;) – Adeel Ansari Dec 10 '10 at 18:40
  • 1
    @AdeelAnsari - sorry for another confusing initialism. I guess I just can't help myself :( – APC Dec 10 '10 at 20:09
3

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.

yallie
  • 2,200
  • 1
  • 28
  • 26
3

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

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • Sorry it was a wrong copy/paste issue. Updated the question. `UTL_COMPRESS` and the link you provided is stating that the compressed data would be GZIP compatible. But what I want is standard ZIP thing. Thanks for pointing out the mistake. – Adeel Ansari Dec 10 '10 at 07:10
  • That link is dead. – zygimantus Sep 27 '16 at 13:24