Zip using Oracle Stored Procedure

oracle stored-procedures plsql zip utility
Adeel Ansari picture Adeel Ansari · Dec 10, 2010 · Viewed 20.4k times · Source

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?

Answer

APC picture APC · Dec 10, 2010

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.

Related questions

Oracle Error ORA-06512

Just can't figure out why it gives me ORA-06512 Error PROCEDURE PX(pNum INT,pIdM INT,pCv VARCHAR2,pSup FLOAT) AS vSOME_EX EXCEPTION; BEGIN IF ((pNum < 12) OR (pNum > 14)) THEN RAISE vSOME_EX; ELSE EXECUTE IMMEDIATE 'INSERT INTO …

Read More
Passing an array of data as an input parameter to an Oracle procedure

I'm trying to pass an array of (varchar) data into an Oracle procedure. The Oracle procedure would be either called from SQL*Plus or from another PL/SQL procedure like so: BEGIN pr_perform_task('1','2','3…

Read More
Truncating a table in a stored procedure

When I run the following in an Oracle shell it works fine truncate table table_name But when I try to put it in a stored procedure CREATE OR REPLACE PROCEDURE test IS BEGIN truncate table table_name; END test; / …

Read More