File Upload to Database Table

File Upload to Database Table

Using below logic we can upload a file from our local machine to oracle custom table.

Step 1: Create Application for Upload File Page.

Step 2: Create project with appropriate name.

Step 3: Create Application Module and a new page (Attach AM to page, Provide Title to page).

Step 4: Create a table with required details and create a synonym for table.

Step 5: Create Entity Object for the table which we created earlier.

Step 6: Create View Object for above created EO (Entity Object).

Step 7: Attach View Object to Application Module.

Step 8: Go to Page and Create Row layout Region and create an item with style as "Message File Upload".

Step 9: Create a submit button in above page.

Step 10: Create a controller for the page and Use below logic in Process Form Request and run the page.

Jdeveloper Page Structure as below

/*===========================================================================+
 |   Copyright (c) 2001, 2005 Oracle Corporation, Redwood Shores, CA, USA    |
 |                         All rights reserved.                              |
 +===========================================================================+
 |  HISTORY                                                                  |
 +===========================================================================*/
package xxbct.oracle.apps.xxbct.BCT_WHT_Invoice_Upload.webui;


import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import java.io.*;
import oracle.cabo.ui.data.DataObject;
import oracle.jbo.Row;
import oracle.jbo.RowSetIterator;
import oracle.jbo.domain.BlobDomain;


/**
 * Controller for ...
 */
public class BCT_WHT_Inv_UploadCO extends OAControllerImpl
{
  public static final String RCS_ID = "$Header$";
  public static final boolean RCS_ID_RECORDED = 
    VersionInfo.recordClassVersion(RCS_ID, "%packagename%");

  /**
   * Layout and page setup logic for a region.
   * @param pageContext the current OA page context
   * @param webBean the web bean corresponding to the region
   */
  public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
  }

  /**
   * Procedure to handle form submissions for form elements in
   * a region.
   * @param pageContext the current OA page context
   * @param webBean the web bean corresponding to the region
   */
  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);
    OAApplicationModule am = pageContext.getApplicationModule(webBean);
    OAViewObject empVO = 
      (OAViewObject)am.findViewObject("View_Name");
    if (pageContext.getParameter("Submit") != null)
    {
      DataObject fileUploadData = pageContext.getNamedDataObject("Upload");
      String fileName;
      String contentType = null;
      Long fileSize = null;
      Integer fileType = new Integer(6);
      BlobDomain uploadedByteStream = null;
      BufferedReader in = null;

      try
      {


        fileName = 
            (String)fileUploadData.selectValue(null, "UPLOAD_FILE_NAME");


        contentType = 
            (String)fileUploadData.selectValue(null, "UPLOAD_FILE_MIME_TYPE");

        uploadedByteStream = 
            (BlobDomain)fileUploadData.selectValue(null, fileName);

        in = 
            new BufferedReader(new InputStreamReader(uploadedByteStream.getBinaryStream()));

        fileSize = new Long(uploadedByteStream.getLength());
        System.out.println((new StringBuilder()).append("file Name").append(fileName).toString());
        System.out.println((new StringBuilder()).append("Content Type").append(contentType).toString());
        System.out.println((new StringBuilder()).append("fileSize").append(fileSize).toString());

      } catch (NullPointerException ex)
      {

        throw new OAException("Please Select a File to Upload", (byte)0);
      }
      try
      {
        String lineReader = "";
        long t = 0L;
        while ((lineReader = in.readLine()) != null)
        {
          if (lineReader.trim().length() > 0)
          {
            System.out.println((new StringBuilder()).append("lineReader").append(lineReader.length()).toString());
            String linetext[] = lineReader.split(",");
            t++;
            if (t != 1L)
            {
              if (!empVO.isPreparedForExecution())
              {
                empVO.setMaxFetchSize(0);
                empVO.executeQuery();
              }
              Row row = empVO.createRow();
              row.setAttribute("InvoiceNumber", linetext[0]);
              row.setAttribute("OrganizationId", linetext[1]);
              row.setAttribute("Attribute1", 
                               am.getOADBTransaction().getSequenceValue("Sequence_Name"));
              empVO.last();
              empVO.next();
              empVO.insertRow(row);
              am.getOADBTransaction().commit();

            }
          }
        }
      } catch (Exception e)
      {
        throw new OAException(e.getMessage(), (byte)0);
      }
      throw new OAException("Excel File Uploaded SuccessFully!!!", (byte)3);
    }

    if (pageContext.getParameter("Reset") != null)
    {
      pageContext.forwardImmediatelyToCurrentPage(null, false, null);
    }

  }
}