Oracle Application Framework

In iRecruitment, it is a common requirement to zip all the documents against an applicant, and then present them in a zipped file to the shortlister.
The best way to achieve zipping is by using Java. Please find the sample code to achieve this.

Where are the documents in iRecruitment Held?
These documents are held in a table named IRC_DOCUMENTS.
This table has a column named BINARY_DOC where documents like MS Word, acrobat files etc are stored.
This article provides a quick sample/code to zip all the documents against any specific applicant/party and to
store the zipped file into a table named xx_temp_blobs.

This sample code runs via an OA Framework page, however you may decide to use this within Java Concurrent Program too.


What are the components of this sample?
A table named xx_temp_blobs, into which documents from irc_documents will be moved after getting zipped.
A PL/SQL Package named xx_zip_irc_documents_pkg, which creates a record with empty blob for the party.
A java controller class xxDoZipCO that will invoke method in ApplicationModule.
An application Module named xxDoZipAMImpl, which actually does the zipping.
A simple OA Framework page named xxDoZipPG.
This page is for testing this functionality, as shown in demo below.


Demo/test Video can be seen from this link

This sample code for OAF can be downloaded from this link.
You can convert this into a Java Concurrent Program if need be.


Summary of Logic
1. Create an empty blob in table. It is in this blob column that we will store the Zipped File
This is done using PL/SQL, via a JDBC call from java
2. Open that record with empty blob in update mode.
3. Link this empty blob to a Zip output stream.
4. Reach each source file to be zipped [ use oracleresultset & getBLOB]
5. Create a ZipEntry for each source file.
Get the binary stream for each BLOB file and write that into "Zip Output Stream"
Effectively you are reading from a binary file and writing into a zip output stream
This zip output stream is linked to the blob column that stores the zipped file.
6. Close the zip output stream - this is an important step



Source Code

--Ideally should be created in custom schema with synonym in apps
CREATE TABLE xx_temp_blobs
(
 party_id INTEGER
,zipped_file BLOB
,last_update_date DATE
,creation_date DATE
,last_updated_by INTEGER
,last_login INTEGER
,conc_request_id NUMBER
) ;



CREATE OR REPLACE PACKAGE xx_zip_irc_documents_pkg AS
  PROCEDURE create_empty_blob(p_party_id IN INTEGER);
END xx_zip_irc_documents_pkg;
/



CREATE OR REPLACE PACKAGE BODY xx_zip_irc_documents_pkg AS
  PROCEDURE create_empty_blob(p_party_id IN INTEGER) IS
    x_empty_blob BLOB;
  BEGIN
    x_empty_blob := empty_blob();
    DELETE FROM xx_temp_blobs
    WHERE party_id = p_party_id;
    INSERT INTO xx_temp_blobs
      (party_id
      ,zipped_file
      ,last_update_date
      ,creation_date
      ,last_updated_by
      ,last_login
      ,conc_request_id)
    VALUES
      (p_party_id
      ,x_empty_blob
      ,SYSDATE
      ,SYSDATE
      ,fnd_global.user_id
      ,fnd_global.login_id
      ,fnd_global.conc_request_id);
  END create_empty_blob;
END xx_zip_irc_documents_pkg;
/


Controller Code
  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);
    OAApplicationModule oam=   pageContext.getApplicationModule(webBean);
    Serializable asr[]={pageContext.getParameter("partyID")};
    oam.invokeMethod("convertToZip",asr) ;
  }



Application Module Code
// Main methods are
//manageZippedEmptyBlob()  - initializes a blob column via PL/SQL Package
//getBlobResultSetFromIRC() - it returns the resultset that contains existing documents
//convertToZip() - Does the actual zipping, it reads each document from irc_documents and writes into ZipOutputStream for each file
package xx.oracle.apps.per.irc.dozip.server;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.zip.Deflater;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.BLOB;

public class xxDoZipAMImpl extends OAApplicationModuleImpl
{
  /**
   *
   * This is the default constructor (do not remove)
   */
  public xxDoZipAMImpl()
  {
  }

  public void xxDebug ( String xMsg )
  {
  System.out.println ( xMsg ) ;
  }
 
  /**
   *
   * Sample main for debugging Business Components code using the tester.
   */
  public static void main(String[] args)
  {
    launchTester("xx.oracle.apps.per.irc.dozip.server", "xxDoZipAMLocal");
  }

  public OracleResultSet manageZippedEmptyBlob (String xPartyId)
  {
  ResultSet rsZippedEmptyBlob= null;
  String sInvokeZippedEmptyBlob="BEGIN xx_zip_irc_documents_pkg.create_empty_blob(:1) ; END;" ;
  xxDebug("Inside manageZippedEmptyBlob()");
  try
  {
  xxDebug("Inside manageZippedEmptyBlob() creating Callable Statement");
  OracleCallableStatement ocs = (OracleCallableStatement)getOADBTransaction().createCallableStatement(sInvokeZippedEmptyBlob,1);
  ocs.setString(1, xPartyId);
  ocs.execute();
  ocs.close();
  getOADBTransaction().commit();
  xxDebug("Inside manageZippedEmptyBlob() Closed Callable Statement"); 
  PreparedStatement ps = getOADBTransaction().createPreparedStatement("select * from xx_temp_blobs where party_id = :1 FOR UPDATE",1 );
  ps.setString(1, xPartyId);
  rsZippedEmptyBlob = ps.executeQuery();
  }
  catch ( Exception ex )
  {
  xxDebug("Exception in manageZippedEmptyBlob() =>" + ex.toString());
  }

  return (OracleResultSet)rsZippedEmptyBlob;
  }

  public ResultSet  getBlobResultSetFromIRC (String xPartyId)
  {
    String sLoopSourceBlob = "select binary_doc,file_name,mime_type from irc_documents where party_id = :1 FOR UPDATE" ;
    ResultSet rLoopSourceBlob =null;
    try
    {
      PreparedStatement pLoopSourceBlob = getOADBTransaction().createPreparedStatement(sLoopSourceBlob,1);
      pLoopSourceBlob.setString(1,xPartyId);
      rLoopSourceBlob= pLoopSourceBlob.executeQuery();
    }
    catch ( Exception ex )
    {
      xxDebug("Exception in convertToZip() =>" + ex.toString());
    }
    return rLoopSourceBlob ;
  }

  public void convertToZip(String xPartyId)
  {
    xxDebug("Inside convertToZip() within AM PartyId=>" + xPartyId  );
    OracleResultSet rsZippedEmptyBlob= manageZippedEmptyBlob (xPartyId) ;
      try
      {
      if ( !rsZippedEmptyBlob.next() )
      {
        return ;
      }
      OracleResultSet rLoopSourceBlob =  (OracleResultSet)getBlobResultSetFromIRC ( xPartyId ) ;
      BLOB zippedEmptyBlob = rsZippedEmptyBlob.getBLOB(2);
      ZipOutputStream zosFinal = new ZipOutputStream ( zippedEmptyBlob.getBinaryOutputStream());
      zosFinal.setLevel(Deflater.DEFAULT_COMPRESSION);
      byte bytesArrayFromSourceBlob[] = new byte[20000];
      while(rLoopSourceBlob.next())
      {
      xxDebug(rLoopSourceBlob.getString(2)); 
      //Create a zip entry for the filename
      ZipEntry zippedSingleFileName = new ZipEntry(rLoopSourceBlob.getString(2)) ;
      //Put this entry of iRecruitment File
      zosFinal.putNextEntry(zippedSingleFileName);
      BLOB sourceBlobFile = rLoopSourceBlob.getBLOB(1);     
      //Now from this source Blob file, we need to get a stream and then write that stream into Zipped Output
      int lengthRead ;
      InputStream ircDocumentStream = sourceBlobFile.getBinaryStream();
      while((lengthRead=ircDocumentStream.read(bytesArrayFromSourceBlob))>0)
            zosFinal.write(bytesArrayFromSourceBlob,0,lengthRead);

      sourceBlobFile.getBinaryStream().close();
      zosFinal.closeEntry();
      }
      zosFinal.close();
      getOADBTransaction().commit();
    }
    catch ( Exception ex )
    {
      xxDebug("Exception in convertToZip() =>" + ex.toString());
    }
   
  }
}

Add comment

Name (required)

E-mail (required, but will not display)

Website

Notify me of follow-up comments

Refresh

Send

Cancel