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.
//---------------------------------------------------------------------------


import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import oracle.jdbc.OracleDriver;

public class ZipLobs {
    public static int writeZip(
                            String   query,
                            Blob[]   outLob) {
      try{
        //ceate the zipoutputstream from the end of the outLob
        Blob  zipLob = outLob[0];
        OutputStream os = zipLob.setBinaryStream(1);
        ZipOutputStream zos = new ZipOutputStream(os);

        //execute the query.
        int chunksize = 32768;
        Connection conn = new OracleDriver().defaultConnection();
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery(query);
        while (rset.next()) {
            // get the id and the data columns
            String filename = rset.getString( 1 );
            Blob src = rset.getBlob( 2);


            //add one zip entry
            ZipEntry entry = new ZipEntry(filename);
            zos.putNextEntry(entry);

            //write data to the zip lob
            long len = src.length();
            long offset = 1;
            byte[] buffer;
            while (offset < len) {
               buffer = src.getBytes(offset, chunksize);
               if (buffer == null) 
                 break;

               zos.write(buffer, 0, buffer.length);  
               offset += buffer.length;
            }
            zos.closeEntry();
        }
        zos.close();

        outLob[0] = zipLob;

      }catch (Exception e) {
          e.printStackTrace(System.out);
          return 1;
      }
      return 0;
    }
}
/

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 number
as 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.

Using the PL/SQL package APEX_MAIL which supports e-mail attachments (as of Application Express 3.1), here's a practical example:

declare
    l_id number;
    l_blob blob;
    l_dummy integer;
begin
    l_id := apex_mail.send( p_to => 'fred.flintstone@bedrock.com',
                            p_from => 'barney.rubble@bedrock.com',
                            p_subj => 'Images',
                            p_body => 'Here are the images I promised -- Barney' ); 
    dbms_lob.createtemporary( l_blob, TRUE );
    l_dummy := ziplobs( 'select filename, blob_content from apex_application_files ' ||
                        'where mime_type like ''%gif%'' and rownum < 5', 
                        l_blob );
    apex_mail.add_attachment( l_id, l_blob, 'ImagesForFred.zip', 'application/zip');
    commit;
end;
/

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.