, ,

R12 – java.sql.SQLException: Invalid column type in OAF

Issue:
When we are using view object (VO) in OA Framework it’s possible that you will run in to the problem java.sql.SQLException: Invalid column type, especially if it’s an LOV VO. When you click on the LOV torch for the first time it is working fine. But whenever when we click on the GO button in the LOV Region it gives an error.

Solution:
What we need to is, change the Binding Style from Oracle Named to Oracle Positional in the View Object declaration. The framework is adding a where clause to the query using bind variables that are typed :n, this is why you need to set Oracle Positional. Now save it run again, then it will solve this issue.

VO Binding Style

,

How to Find Duplicate Row in OAF Page?

How to Find Duplicate Row in OAF Page?

In this article, I have explained how to stop user to enter duplicate rows in OAF Page table region or advanced table region. This validation helps the users not to enter any duplicate record into system. Below method should be created in AM (Application Module) and it can be referred from CO.

Below method is used to compare rows from same VO Object with different VO instance. (mainVO and Subvo).

public void XXDuplicateVendorCharges() {

  OAViewObject mainVO =
  getXXVendorChargesEOVO1();
  OAViewObject Subvo =
  getXXVendorChargesEOVO1();

  for (Row row = mainVO.first(); row != null; row = mainVO.next()) {
    Number mainVOVendorChargesId = (Number)row.getAttribute("VendorChargesId");
    String mainChrgType = (String)row.getAttribute("VendorChargeType");

    for (Row row1 = Subvo.first(); row1 != null;
      row1 = Subvo.next()) {
      Number SubvoVendorChargesId = (Number)row1.getAttribute("VendorChargesId");
      String SubChrgType = (String)row1.getAttribute("VendorChargeType");

      if (!(mainVOVendorChargesId.equals(SubvoVendorChargesId))) {
        if ((mainChrgType).equals(SubChrgType)) {
          //Subvo.remove(); //remove the Child VO
          //getTransaction().commit(); // This performs the actual delete.
          throw new OAException("Duplicate row exists in Vendor Charges, select only unique Vendor Charge Type.",
                                        OAException.ERROR);
        }
      }
    } //SubVO
  } //MainVO
  mainVO.first();
  Subvo.clearCache();
}

Another Method to check duplicate from DB Object like below.

public void XXDuplicateVendorCharges() {

    OAViewObject VendorHdrVO = getXXVendorHeaderEOVO1();
    Row VendorHdrRow = VendorHdrVO.getCurrentRow();

    try {
        if (VendorHdrRow != null) {
            VendorHdrId = (Number)VendorHdrVO.getAttribute("VendorHdrId");
        }
    } catch (Exception exec) {
        throw new OAException("Error: " + exec, OAException.ERROR);
    }

    OAViewObject mainVO =  getXXVendorChargesEOVO1(); 
    XXVendorChargesEOVOImpl Subvo = 
        (XXVendorChargesEOVOImpl)this.findViewObject("getXXVendorChargesEOVO2"); //Child VO for duplicate checking
        
    if (Subvo == null) {
        Subvo = (XXVendorChargesEOVOImpl)this.createViewObject("getXXVendorChargesEOVO2", 
                                                                   "xxerp.oracle.apps.po.customapp.server.XXVendorChargesEOVO");
    }
    Subvo.clearCache();

    if (!(Subvo.isPreparedForExecution())) {
        Subvo.setWhereClause(null);
        Subvo.setWhereClause("VENDOR_HDR_ID= " + "'" + VendorHdrId + "'");
        Subvo.executeQuery();
    }


    for (Row row = mainVO.first(); row != null; row = mainVO.next()) {
        Number mainVOVendorChargesId = 
            (Number)row.getAttribute("VendorChargesId");
        String mainChrgType = (String)row.getAttribute("VendorChargeType");
       
        for (Row row1 = clonevo.first(); row1 != null; 
             row1 = Subvo.next()) {
            Number SubvoVendorChargesId = 
                (Number)row1.getAttribute("VendorChargesId");
            String SubChrgType = (String)row1.getAttribute("VendorChargeType");

            if (!(mainVOVendorChargesId.equals(SubvoVendorChargesId))) {
                if ((mainChrgType).equals(SubChrgType)) {
                    //Subvo.remove(); //remove the Child VO
                    //getTransaction().commit(); // This performs the actual delete.
                    throw new OAException("Duplicate row exists in Vendor Charges, select only unique Vendor Charge Type.", 
                                          OAException.ERROR);
                }
            }
        } //Subvo
    } //MainVO
    mainVO.first();
    Subvo.remove();
}

 

How to Create DBC file in oracle apps R12 EBS?

How to Create DBC file in oracle apps R12 EBS?

The .dbc file is mostly used to define database parameters,stands for database connect descriptor file, used to connect-to database, it authenticate users against database in FND_USER table.

DBC file is quite important as whenever Java or any other program like forms want to connect to database it uses dbc file.

For OAF page development, we need to get the DBC file of development environment.

Method 1:

//Login as Application user
Navigate to $INST_TOP/admin/install directory and run adgendbc.sh script.

sh adgendbc.sh

Run the command adgendbc.sh, It will generate .dbc file under $INST_TOP/appl/fnd/12.0.0/secure

Method 2:

//Login as Application user

java oracle.apps.fnd.security.AdminAppServer apps/apps ADD FNDNAM=apps GWYUID=applsyspub/pub TWO_TASK= SECURE_PATH=$FND_TOP/secure GUEST_USER_PWD=guest/oracle APPS_JDBC_DRIVER_TYPE=THIN DB_HOST=vis.oracleerpappsguide.com DB_PORT=1521

Method 3: (Via Browser URL)

Below locations we can find in DBC files in system.
For 11i:

cd $FND_TOP/secure

For R12:

cd $FND_SECURE

In case, if we don’t have access to the server we can get it with other easy way.

Open a web browser and go to the homepage Application URL and append “/OA_HTML/jsp/fnd/aoljtest.jsp” to it like below.

http://vis.oracleerpappsguide.com:8000/OA_HTML/jsp/fnd/aoljtest.jsp

This will open the below page and enter the required details.

Enter DB Details

If all the details are correct entered you will get an overview page with all details, and at bottom of screen you will get a link “Enter AOL/J Setup Test”. click that link.

DB Instance Details

This will redirect you to page from where you can Locate DBC file under connection test from left side Menu list.

DBC File
On the right side you will get the contents of the DBC file. Copy/paste this to a VISION.dbc file and place it in your JDeveloper JDEV_USER_HOME/dbc_files/secure.