Turning customers into rockstars, with the Oracle Database, PL/SQL and Oracle Application Express.

Wednesday, April 16, 2008

Zip it!

For those of you thinking this is a posting about Dr. Evil from the Austin Powers movie, you'll be disappointed. However, if you have ever wanted a way to create a zip file in PL/SQL in an Oracle database, then this one is for you.

This question has come up many times over the years. There are many ways to export files from Oracle Application Express (applications, images, data in XML, etc.), but wouldn't it be great if this could be downloaded as a single file, or at a minimum, as a zip archive of these files? Of course - but I was never aware of a simple way to pull this off.

Then along comes the Oracle Multimedia team (a supremely competent and professional team - led by Sue Mavris) . At Oracle OpenWorld last year, the Oracle Multimedia team demonstrated DICOM support in the 11gR1 database. They created a couple applications in Application Express for this demonstration. I was recently reviewing these applications and I came across an interesting piece of code which does the creation of a zip archive for any number of BLOBs. All credit for this code must be given to Fengting Chen from the Oracle Multimedia team - I just modified it slightly for this posting.

This solution requires the Java VM in the Oracle database. I tried this out on an 11gR1 11.1.0.6 database, but I imagine this may work for earlier database versions as well. It's nothing more than a Java class that is compiled into the database and then a call specification which is used to publish the Java method to SQL and PL/SQL. It's unbelievably elegant.

Using either SQL*Plus or SQL Commands in SQL Workshop, run the following script:

create or replace java source named ZipLobs as

//---------------------------------------------------------------------------// File name: ZipLobs_java.sql// Description: Java Stored Procedure that zips the data of BLOBS returned// by the query and writes into a destintation BLOB.//---------------------------------------------------------------------------

After this is compiled, it will create an object of type JAVA SOURCE as well as another object of type JAVA CLASS, both of which may initially be invalid. To correct this, issue the statement:

alter java source ziplobs compile;

Lastly, create the call specification to make this class visible from PL/SQL:

-- -- The zip utility that zips data in multiple BLOBs and returns -- into a single BLOB -- -- Parameters: -- p_query: The SQL query which must select the filename as the first -- column and the BLOB data to be zipped as the second column -- -- p_zip: BLOB variable which will contain the contents of the zipped BLOBS -- -- Return 0: Success -- 1: Error occured -- -- Example: -- -- ziplobs('select fname, resume from docs where id < 10', l_zip ); --create or replace function ziplobs( p_query in varchar2, p_zip in out nocopy blob )return numberas language java name 'ZipLobs.writeZip( java.lang.String, java.sql.Blob[]) return int';/

That's all there is to it! Now you have a procedure which you can use to zip all sorts of things. You could use this to zip up a collection of PDF's and present it as a single file download to the end user. You could author a database job which uses DBMS_XMLGEN to generate XML files for the data in your tables, zip all of them up, and store it directly in the database. You could write a procedure which takes a number of files, zips them up, and e-mails them.

While I would like to think that this would eventually make it into a distribution of Application Express, the biggest impediment is the dependency upon Java in the database. We removed this dependency beginning with Application Express 2.2, at the request of a number of customers. But with that said, I'm sure this will make its way into a number of packaged applications and other utilities provided by our team.

Once I had similar problem and solved it with help next software-how to repair a corrupted zip file,as far as I can see program successefully works with zip files and also more than,it is free in fact,utility compatible with all Windows family: Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 and Windows Vista,will open your file with *.zip extension or SFX self-extracting archive and analyze compressed documents,can work on old PC's, recovery time will be much longer, when comparing with more powerful PC's, because except file size recovery process depends on CPU performance.

About Me

My name is Joel Kallman. I am the Director of Software Development at Oracle, and I manage the development and product management of Application Express (APEX) at Oracle. I've been at Oracle since 1996, and my VP Mike Hichwa and I created Application Express in 1999. My passion is to make customers extraordinarily successful with the Oracle Database, PL/SQL and Oracle Application Express.