I am stuck in a java based issue assigned to me.
I have an Oracle Integrator (Web ADI) that uploads records from excel based template to Oracle Apps. There are some java based List of Values (LOVs) attached to the template. I am having an issue in these LOVs as the code is poorly written. Up to 450 records are successfully being uploaded but when we increase the load to 500, it gives following error: "BneBaseSQL.executeQuery: Stack trace: java.sql.SQLException: ORA-01000: maximum open cursors exceeded"
I have tried changing the open_cursors limit to 1000 which resolved my issue but we may have bigger loads upto 5000 records. So we want to resolve the issue in code.
I analyzed the code and came to know that error is in Validator class. Actually, all records should be picked up in one go and then can be used from result set. But as I can understand, its going to pick a single record and performs action and in this scenario opening so many cursors which exceeds the limit of db open_cursors.
Can anybody please have a look at the code and advise how I can rewrite my validator.
I am attaching sql and validator code.
package oracle.apps.integrators.apinv.sql;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.utilities.sql.BneBaseSQL;
public class apinvCostCtrSQL extends BneBaseSQL
{
public apinvCostCtrSQL(BneWebAppsContext paramBneWebAppsContext, String paramString)throws
SQLException, BneException
{
Connection connection = paramBneWebAppsContext.getJDBCConnection();
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append(
"select cost_centre, description from ( " +
//--ITEM/MISC accounts
"select fv.flex_value cost_centre, fv.description " +
"from gl_access_sets acs ,fnd_id_Flex_segments fifs, fnd_flex_values_vl fv " +
"where to_char(acs.access_set_id) = fnd_profile.value('GL_ACCESS_SET_ID') " +
“and to_char(acs.chart_of_accounts_id) = fifs.id_flex_num and fifs.id_flex_code = 'GL#' " +
"and fv.summary_flag = 'N' and fifs.segment_num = 2 " +
"and fifs.flex_value_set_id = fv.flex_value_set_id " +
"and ( (:1 in ('ITEM', 'MISCELLANEOUS', 'FREIGHT') ) ) " +
" union all " +
//--tax
"select fv.flex_value cost_centre, fv.description " +
"from gl_access_sets acs ,fnd_id_Flex_segments fifs, fnd_flex_values_vl fv, " +
"zx_rates_vl zxr, zx_accounts b, hr_operating_units hou, " +
"gl_code_combinations cc " +
"where to_char(acs.access_set_id) = fnd_profile.value('GL_ACCESS_SET_ID') " +
"and to_char(acs.chart_of_accounts_id) = fifs.id_flex_num and fifs.id_flex_code = 'GL#' " +
"and fv.summary_flag = 'N' and fifs.segment_num = 2 and fv.enabled_flag = 'Y' " +
"and fifs.flex_value_set_id = fv.flex_value_set_id " +
"and :2 = 'TAX' " +
"and fv.flex_value = cc.segment2 " +
"and b.internal_organization_id = hou.organization_id " +
"AND b.tax_account_entity_code = 'RATES' " +
"AND b.tax_account_entity_id = zxr.tax_rate_id " +
"AND zxr.active_flag = 'Y' " +
"AND SYSDATE BETWEEN zxr.effective_from AND nvl(zxr.effective_to, SYSDATE) " +
"and b.tax_account_ccid = cc.code_combination_id " +
" and hou.name = :3 " + //--ou_name
" and zxr.tax = :4 " +//--tax_code --''
" ) where 1 = 1 "//closing inline query);
if ((paramString != null) && (!paramString.trim().equals("")))
{
stringBuffer.append("AND " + paramString);
}
stringBuffer.append(" ORDER BY cost_centre ");
setQuery(connection, stringBuffer.toString());
} //// ---- END --- public apinvCostCtrSQL
} //// ---- END --- public class apinvCostCtrSQL
package .oracle.apps.integrators.apinv.validators;
import .oracle.apps.integrators.apinv.sql.apinvCostCtrSQL;
import java.text.SimpleDateFormat;
import java.util.Enumeration;
import java.util.Hashtable;
import oracle.apps.bne.exception.BneException;
import oracle.apps.bne.exception.BneFatalException;
import oracle.apps.bne.exception.BneMissingParameterException;
import oracle.apps.bne.framework.BneLogger;
import oracle.apps.bne.framework.BneWebAppsContext;
import oracle.apps.bne.integrator.upload.BneUploadColumn;
import oracle.apps.bne.integrator.upload.BneUploaderMessage;
import oracle.apps.bne.integrator.validators.BneUploadValidator;
import oracle.apps.bne.utilities.BneStringUtils;
import oracle.apps.bne.utilities.sql.BneCompositeSQLCriteria;
import oracle.apps.bne.utilities.sql.BneResultSet;
import oracle.apps.bne.utilities.sql.BneSQLStatement;
public class apinvCostCtrValidator extends BneUploadValidator
{
public BneUploaderMessage[] startupValidator(BneWebAppsContext bnewebappscontext, Hashtable
requestParameters, Hashtable columnValues)
{
m_Logger.log(7, "apinvCostCtrValidator.startupValidator() Start DDD");
m_Statements = new Hashtable();
m_Logger.log(7, "apinvCostCtrValidator..startupValidator() End DDD");
return new BneUploaderMessage[0];
}
public String[] getDomainParameters()
{
return new String[] { "P_LINE_TYPE", "P_OPERATING_UNIT", "P_TAX_CODE" };
}
public BneResultSet getDomainValues(BneWebAppsContext paramBneWebAppsContext,
Hashtable paramHashtable, BneCompositeSQLCriteria paramBneCompositeSQLCriteria) throws
BneException
{
apinvCostCtrSQL sagarSQL = null;
BneResultSet bneResultSet = null;
BneSQLStatement bneSQLStatement1 = new BneSQLStatement();
if (paramBneCompositeSQLCriteria != null)
{
bneSQLStatement1 = paramBneCompositeSQLCriteria.evaluate(bneSQLStatement1);
}
String lineType = (String)paramHashtable.get("P_LINE_TYPE");
String operatingUnit = (String)paramHashtable.get("P_OPERATING_UNIT");
String taxCode = (String)paramHashtable.get("P_TAX_CODE");
if (lineType == null || operatingUnit == null ||taxCode == null)
{
throw new BneMissingParameterException("Operating Unit, Tax code and LineType must be supplied");
}
try
{
sagarSQL = new apinvCostCtrSQL(paramBneWebAppsContext, bneSQLStatement1.getStatement());
//Calling query - passing parameters
BneSQLStatement bneSQLStatement2 = new BneSQLStatement(sagarSQL.getQuery(),
new Object[] { lineType, lineType, operatingUnit, taxCode});
bneSQLStatement2.append("", bneSQLStatement1.getBindValues());
bneResultSet = sagarSQL.getBneResultSet(bneSQLStatement2.getBindValuesAsArray());
}
catch (Exception exception)
{
throw new BneFatalException(exception.toString());
}
return bneResultSet;
} ////----- END --- public BneResultSet getDomainValues
public BneUploaderMessage[] validateColumn(BneWebAppsContext bnewebappscontext, Hashtable
requestParameters, Hashtable columnValues, BneUploadColumn currentColumn)
{
m_Logger.log(7, "apinvCostCtrValidator.validateColumn() Start GGG");
SimpleDateFormat tempDateFormat = new SimpleDateFormat("yyyy-MM-dd");
tempDateFormat.setLenient(false);
String lineType = null;
String costCentreCode = null;
String operatingUnit = null;
String taxCode = null;
String key = null;
Object sqlHandleObject = null;
Object[] row = null;
apinvCostCtrSQL SQLHandle = null;
String interfaceName = currentColumn.getInterfaceName();
BneSQLStatement extraSQL = new BneSQLStatement(" cost_centre = :5");
CostCentreCode = (String)currentColumn.getColumnValueAsObject();
m_Logger.log(7, "apinvCostCtrValidator.validateColumn cost Centre code: " + costCentreCode);
// Getting other items for validation.
BneUploadColumn lineTypeColumn = (BneUploadColumn)columnValues.get
(BneStringUtils.concatSeperatorUnlessEmpty(interfaceName, "P_LINE_TYPE", "."));
lineType = (String)lineTypeColumn.getColumnValue();
BneUploadColumn operatingUnitColumn =
(BneUploadColumn)columnValues.get(BneStringUtils.concatSeperatorUnlessEmpty
(interfaceName, "P_OPERATING_UNIT", "."));
operatingUnit = (String)operatingUnitColumn.getColumnValue();
BneUploadColumn taxCodeColumn =
(BneUploadColumn)columnValues.get(BneStringUtils.concatSeperatorUnlessEmpty
(interfaceName, "P_TAX_CODE", "."));
taxCode = (String)taxCodeColumn.getColumnValue();
m_Logger.log(7, "apinvCostCtrValidator.validateColumn line type: " + lineType);
try
{
key = lineType + "," + operatingUnit + "," + taxCode + "," + costCentreCode;
m_Logger.log(7, "apinvCostCtrValidator.validateColumn key = " + key);
sqlHandleObject = m_Statements.get(key);
SQLHandle = null;
if(sqlHandleObject != null)
{
SQLHandle = (apinvCostCtrSQL)sqlHandleObject;
}
else
{
SQLHandle = new apinvCostCtrSQL(bnewebappscontext, extraSQL.getStatement());
m_Statements.put(key, SQLHandle);
}
// PART 2 OF PARAMETERS FOR QUERY Incl value to validate
row = SQLHandle.getFirstRow(new Object[] {lineType, lineType, operatingUnit, taxCode,
costCentreCode});
m_Logger.log(7, "apinvCostCtrValidator.validateColumn() got " + ((Integer)row[0]).intValue());
if(((Integer)row[0]).intValue() > 0)
{
// keep return statement, remove others.
currentColumn.flagColumnAsFineGrainValid();
m_Logger.log(7, "apinvCostCtrValidator.validateColumn() End GGG - valid");
return new BneUploaderMessage[0];
}
else
{
m_Logger.log(7, "apinvCostCtrValidator.validateColumn() End GGG - invalid");
return uploadError(currentColumn, "The Cost Centre is invalid: " +
costCentreCode, "apinvCostCtrValidator");
}
}
catch(Exception exception)
{
m_Logger.log(7, "apinvCostCtrValidator.validateColumn() End GGG exception");
return uploadException(currentColumn,"Unexpected error during Cost Centre
Validation", "apinvCostCtrValidator",exception);
}
} //// ---- END --- public BneUploaderMessage[] validateColumn
public BneUploaderMessage[] shutdownValidator()
{
m_Logger.log(7, "apinvCostCtrValidator.shutdownValidator() Start HHH");
if(m_Statements != null)
{
Object k;
for(Enumeration stmts = m_Statements.keys(); stmts.hasMoreElements(); m_Statements.remove(k))
{
k = stmts.nextElement();
Object o = m_Statements.get(k);
apinvCostCtrSQL SQLHandle = (apinvCostCtrSQL)o;
SQLHandle.close();
}
m_Statements = null;
}
m_Logger.log(7, "apinvCostCtrValidator.shutdownValidator() End HHH");
return new BneUploaderMessage[0];
}
public apinvCostCtrValidator()
{
m_Logger = BneLogger.getInstance();
m_Logger.log(7, "apinvCostCtrValidator init completed XYZ");
}
private BneLogger m_Logger;
private Hashtable m_Statements;
} //// ---- END --- public class apinvCostCtrValidator
Thanks in advance. MH
