4 Replies Latest reply on Mar 30, 2015 7:58 PM by 2725480

    Invalid column index: JBO-27122: SQL error during statement preparation

    2725480

      Hi,

       

      I am getting below exception in my extended OAF page:

       

      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.  Statement: SELECT OpportunityEO.lead_id ,  OpportunityEO.last_update_date as LastUpdateDate, OpportunityEO.creation_date , OpportunityEO.lead_number, OpportunityEO.customer_id,  NVL(OpportunityEO.TOTAL_AMOUNT,0) as TotalAmount, OpportunityEO.Decision_Date, OpportunityEO.win_probability , OpportunityEO.description as Description,  OpportunityEO.Currency_Code, OpportunityEO.Source_Promotion_Id , OpportunityEO.OWNER_SALESFORCE_ID,  OpportunityEO.TOTAL_REVENUE_OPP_FORECAST_AMT , null as VehicleResponseCode,  ast.meaning as OpptyStatusName, null as CurrencyName,  null as SalesStageName,  hp.party_name as PartyName, null as CustomerCategory,  null as PersonId,  null as ContactName,  null as PersonFirstName,  null as PersonLastName,  null as PersonMiddleName,  null as Salutation,  null as EmailAddress,  null as JobTitle,   null  as PhoneCountryCode,  null as PhoneAreaCode,  null as PhoneNumber , null as PhoneExtension , null as PhoneLineType , to_number(null) as ContactPointId ,null as FormattedPhone,  OpportunityEO.attribute1 as State , hp.attribute4 as City, (case WHEN length(OpportunityEO.attribute13) != 19 THEN null ELSE fnd_date.canonical_to_date(OpportunityEO.attribute13) END) as Country, (case WHEN length(OpportunityEO.attribute14) != 19 THEN null ELSE fnd_date.canonical_to_date(OpportunityEO.attribute14) END) as Province, null as PostalCode,  to_number(null) as ContactLocationId,null as Address,  null as SourceName,  jrt.resource_name as ResourceName, null as SalesChannel, null as RelationShipId,  null as PrimaryContactPartyId,  null as CustState,  null as CustCity,  null as CustCountry,  null as CustProvince,  null as CustPostalCode,  to_number(null) as CustomerLocationId,  null as CustAddress,  null as OpptyUpdatedBy,  null as OpptyCreatedBy,  null as MethodologyNm,  null as CloseReason,  null as AssignmentStatusNm,  OpportunityEO.PRM_Referral_Code as ReferralCode , null  as PrimaryPhoneCountryCode,  null as PrimaryPhoneAreaCode,  null as PrimaryPhoneNumber , null as PrimaryPhoneExtension , null as PrimaryPhoneLineType , to_number(null) as CustomerContactPointId ,null as CustFormattedPhone,  null as CustEmailAddress,  null as ConvAmt,  null as ConvFrcstAmt,  null as SourceCode FROM  as_leads_all  OpportunityEO  ,( SELECT /*+ no_merge */ distinct aaa.lead_id    FROM  as_accesses_all aaa  WHERE  aaa.sales_lead_id IS  NULL  AND    aaa.lead_id IS NOT NULL  AND   aaa.open_flag = 'Y'  AND   aaa.salesforce_id  = :0) secu  ,  as_statuses_tl ast  ,  as_lead_contacts_all alca, hz_parties hcp   ,fnd_territories_tl ftt  ,hz_parties hp  ,  jtf_rs_resource_extns_tl jrt  WHERE   OpportunityEO.lead_id = secu.lead_id  AND OpportunityEO.status = ast.status_code   AND ast.language = USERENV('LANG') AND  OpportunityEO.lead_id = alca.lead_id   AND  OpportunityEO.customer_id = alca.customer_id   AND  alca.primary_contact_flag = 'Y'  AND  alca.contact_party_id = hcp.party_id(+)  AND hcp.country = ftt.territory_code   AND ftt.language = USERENV('LANG')  AND   OpportunityEO.customer_id = hp.party_id   AND OpportunityEO.owner_salesforce_id = jrt.resource_id  AND jrt.language = USERENV('LANG') ORDER BY  LastUpdateDate DESC

        at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)

        at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)

        at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)

        at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)

        at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:733)

        at oracle.apps.asn.opportunity.webui.ASNOpptyLstCO.executeOpptyQuery(Unknown Source)

        at oracle.apps.xxfe.f486.asn.opportunity.webui.ASNOpptyLstCOEx.executeOpptyQuery(ASNOpptyLstCOEx.java:335)

        at oracle.apps.asn.opportunity.webui.ASNOpptyLstCO.processFormRequest(Unknown Source)

        at oracle.apps.xxfe.f486.asn.opportunity.webui.ASNOpptyLstCOEx.processFormRequest(ASNOpptyLstCOEx.java:134)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:854)

        at oracle.apps.fnd.framework.webui.OATableHelper.processFormRequest(OATableHelper.java:460)

        at oracle.apps.fnd.framework.webui.beans.table.OATableBean.processFormRequest(OATableBean.java:1058)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.layout.OAHeaderBean.processFormRequest(OAHeaderBean.java:410)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.OAQueryHelper.processFormRequest(OAQueryHelper.java:1035)

        at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.beans.OAFlexibleContentBean.processFormRequest(OAFlexibleContentBean.java:372)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.beans.layout.OAFlexibleLayoutBean.processFormRequest(OAFlexibleLayoutBean.java:376)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1319)

        at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)

        at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:3172)

        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1927)

        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:567)

        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:455)

        at _pages.__oa._jspService(__oa.java:233)

        at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)

        at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)

        at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)

        at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)

        at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.fnd.security.WLFilter.doFilter(WLFilter.java:213)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:141)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(Unknown Source)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:432)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119)

        at java.security.AccessController.doPrivileged(Native Method)

        at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:324)

        at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:460)

        at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103)

        at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171)

        at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:163)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3748)

        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3714)

        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)

        at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)

        at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2283)

        at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2182)

        at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1491)

        at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)

        at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)

      ## Detail 0 ##

      java.sql.SQLException: Invalid column index

        at oracle.jdbc.driver.OraclePreparedStatement.setNUMBERInternal(OraclePreparedStatement.java:8141)

        at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:10791)

        at oracle.jdbc.driver.OraclePreparedStatement.setCustomDatumInternal(OraclePreparedStatement.java:10401)

        at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:11655)

        at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:11631)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:253)

        at weblogic.jdbc.wrapper.PreparedStatement.setObject(PreparedStatement.java:357)

        at oracle.jbo.server.BaseSQLBuilderImpl.bindParamValue(BaseSQLBuilderImpl.java:1847)

        at oracle.jbo.server.OracleSQLBuilderImpl.bindParamValue(OracleSQLBuilderImpl.java:3940)

        at oracle.jbo.server.BaseSQLBuilderImpl.bindParametersForStmt(BaseSQLBuilderImpl.java:3335)

        at oracle.jbo.server.ViewObjectImpl.bindParametersForCollection(ViewObjectImpl.java:14008)

        at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:804)

        at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)

        at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)

        at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)

        at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4591)

        at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:751)

        at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:900)

        at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:814)

        at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:808)

        at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3674)

        at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:439)

        at oracle.apps.xxfe.f486.asn.opportunity.server.ASNOpptyQryAMExImpl.initOpptyQuery(ASNOpptyQryAMExImpl.java:514)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:606)

        at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)

        at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:733)

        at oracle.apps.asn.opportunity.webui.ASNOpptyLstCO.executeOpptyQuery(Unknown Source)

        at oracle.apps.xxfe.f486.asn.opportunity.webui.ASNOpptyLstCOEx.executeOpptyQuery(ASNOpptyLstCOEx.java:335)

        at oracle.apps.asn.opportunity.webui.ASNOpptyLstCO.processFormRequest(Unknown Source)

        at oracle.apps.xxfe.f486.asn.opportunity.webui.ASNOpptyLstCOEx.processFormRequest(ASNOpptyLstCOEx.java:134)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:854)

        at oracle.apps.fnd.framework.webui.OATableHelper.processFormRequest(OATableHelper.java:460)

        at oracle.apps.fnd.framework.webui.beans.table.OATableBean.processFormRequest(OATableBean.java:1058)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.layout.OAHeaderBean.processFormRequest(OAHeaderBean.java:410)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.OAQueryHelper.processFormRequest(OAQueryHelper.java:1035)

        at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.beans.OAFlexibleContentBean.processFormRequest(OAFlexibleContentBean.java:372)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.beans.layout.OAFlexibleLayoutBean.processFormRequest(OAFlexibleLayoutBean.java:376)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1319)

        at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)

        at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:3172)

        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1927)

        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:567)

        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:455)

        at _pages.__oa._jspService(__oa.java:233)

        at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)

        at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)

        at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)

        at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)

        at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.fnd.security.WLFilter.doFilter(WLFilter.java:213)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:141)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(Unknown Source)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:432)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119)

        at java.security.AccessController.doPrivileged(Native Method)

        at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:324)

        at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:460)

        at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103)

        at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171)

        at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:163)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3748)

        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3714)

        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)

        at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)

        at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2283)

        at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2182)

        at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1491)

        at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)

        at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)

      java.sql.SQLException: Invalid column index

        at oracle.jdbc.driver.OraclePreparedStatement.setNUMBERInternal(OraclePreparedStatement.java:8141)

        at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:10791)

        at oracle.jdbc.driver.OraclePreparedStatement.setCustomDatumInternal(OraclePreparedStatement.java:10401)

        at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:11655)

        at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:11631)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:253)

        at weblogic.jdbc.wrapper.PreparedStatement.setObject(PreparedStatement.java:357)

        at oracle.jbo.server.BaseSQLBuilderImpl.bindParamValue(BaseSQLBuilderImpl.java:1847)

        at oracle.jbo.server.OracleSQLBuilderImpl.bindParamValue(OracleSQLBuilderImpl.java:3940)

        at oracle.jbo.server.BaseSQLBuilderImpl.bindParametersForStmt(BaseSQLBuilderImpl.java:3335)

        at oracle.jbo.server.ViewObjectImpl.bindParametersForCollection(ViewObjectImpl.java:14008)

        at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:804)

        at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)

        at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3754)

        at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)

        at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4591)

        at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:751)

        at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:900)

        at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:814)

        at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:808)

        at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3674)

        at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:439)

        at oracle.apps.xxfe.f486.asn.opportunity.server.ASNOpptyQryAMExImpl.initOpptyQuery(ASNOpptyQryAMExImpl.java:514)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:606)

        at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(Unknown Source)

        at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:733)

        at oracle.apps.asn.opportunity.webui.ASNOpptyLstCO.executeOpptyQuery(Unknown Source)

        at oracle.apps.xxfe.f486.asn.opportunity.webui.ASNOpptyLstCOEx.executeOpptyQuery(ASNOpptyLstCOEx.java:335)

        at oracle.apps.asn.opportunity.webui.ASNOpptyLstCO.processFormRequest(Unknown Source)

        at oracle.apps.xxfe.f486.asn.opportunity.webui.ASNOpptyLstCOEx.processFormRequest(ASNOpptyLstCOEx.java:134)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:854)

        at oracle.apps.fnd.framework.webui.OATableHelper.processFormRequest(OATableHelper.java:460)

        at oracle.apps.fnd.framework.webui.beans.table.OATableBean.processFormRequest(OATableBean.java:1058)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.layout.OAHeaderBean.processFormRequest(OAHeaderBean.java:410)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.OAQueryHelper.processFormRequest(OAQueryHelper.java:1035)

        at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.beans.OAFlexibleContentBean.processFormRequest(OAFlexibleContentBean.java:372)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.beans.layout.OAFlexibleLayoutBean.processFormRequest(OAFlexibleLayoutBean.java:376)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.layout.OAStackLayoutBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1319)

        at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(Unknown Source)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1066)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1032)

        at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:887)

        at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:407)

        at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)

        at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:3172)

        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1927)

        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:567)

        at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:455)

        at _pages.__oa._jspService(__oa.java:233)

        at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)

        at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)

        at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)

        at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:301)

        at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.fnd.security.WLFilter.doFilter(WLFilter.java:213)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.jtf.cabo.interceptor.JTFWrapperFilter.doFilter(JTFWrapperFilter.java:141)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(Unknown Source)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:432)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:119)

        at java.security.AccessController.doPrivileged(Native Method)

        at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:324)

        at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:460)

        at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:103)

        at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:171)

        at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:163)

        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:60)

        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3748)

        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3714)

        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)

        at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)

        at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2283)

        at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2182)

        at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1491)

        at weblogic.work.ExecuteThread.execute(ExecuteThread.java:256)

        at weblogic.work.ExecuteThread.run(ExecuteThread.java:221)

       

      Could any one please help me to trap this bug?

       

      Thanks,

      Anupam

        • 1. Re: Invalid column index: JBO-27122: SQL error during statement preparation
          aJohny

          Please let us what you are trying to do.

           

          Show us your code without that it is hard to help.

           

          Cheers

          AJ

          • 2. Re: Invalid column index: JBO-27122: SQL error during statement preparation
            2725480

            Thanks AJ.

             

            Here is the code initOpptyQuery method defined in ASNOpptyQryAMExImpl class :

             

            I have marked the line where the error is coming in bold and underline (in the last of the code)

             

            Regards,

            Anupam

             

            public void initOpptyQuery(HashMap hashmap, ArrayList arraylist, HashMap hashmap1, HashMap hashmap2, Boolean boolean1)

                {

                    String s = "xxfe.f486.asn.opportunity.server.ASNOpptyQryAMExImpl.initOpptyQuery";

                    OADBTransaction oadbtransaction = getOADBTransaction();

                    boolean flag = oadbtransaction.isLoggingEnabled(2);

                    String l_step = "";

                    int i = 0;

                    new StringBuffer(100);

                    if(flag)

                        oadbtransaction.writeDiagnostics(s, "Begin", 2);

             

             

                   ASNOpptyQryAMImpl asnopptyqryamimpl = this;

                    java.util.Dictionary adictionary[] = (java.util.Dictionary[])hashmap.get("ASNReqOpptyQryCrtra");

             

             

             

                    OpptySearchManager opptysearchmanager = new OpptySearchManager();

             

             

                    boolean flag1 = boolean1.booleanValue();

                    boolean l_changeStatusCode = true;

             

             

                      if(flag)

                       oadbtransaction.writeDiagnostics(s, "xxfe start to check of Status Category param...!", 2);

                      

                    String sCriteriaName = "";     

                   

                    // put search criteria to hashmap

                    for(i = 0; i < adictionary.length; i++)

                    {

             

             

                      Dictionary dictionary = adictionary[i];

                      try

                      {

                      l_step = "get criteria item Name for i=" + i;

                      sCriteriaName = (String)dictionary.get("criteriaItemName");

                      l_step = "get criteria item Value for i=" + i;

                      Object obj3 = dictionary.get("criteriaValue");         

                      l_step = "set criteria item Value to String i=" + i;

             

             

                   

                      String sValue= (String)obj3;

                      l_step = "get criteriaViewAttributeName i=" + i;         

                      String s6 = (String)dictionary.get("criteriaViewAttributeName");

                      l_step = "get criteriaCondition i=" + i;          

                      String s7 = (String)dictionary.get("criteriaCondition");

                      l_step = "get criteriaJoinCondition i=" + i;          

                      String s8 = (String)dictionary.get("criteriaJoinCondition");

                      // output search criteria info

                      l_step = "output search criteria info i=" + i;       

                      if(flag)

                          {

                          oadbtransaction.writeDiagnostics(s, "xxfe Criteria: "  + i + " name=" + sCriteriaName, 2);

                          oadbtransaction.writeDiagnostics(s, "xxfe criteriaViewAttributeName: "  + s6 + " criteriaCondition=" + s7 + "; criteriaJoinCondition="+s8, 2);

                          }

                      }

                      catch (Exception xxfeEx)

                        {

                              if(flag)

                              {

                              oadbtransaction.writeDiagnostics(s, "xxfe Warning! in getting search criteria values. Criteria name = " + sCriteriaName + ". Step is "  + l_step, 2); 

                              oadbtransaction.writeDiagnostics(s,  "WarningMessage=" + xxfeEx.getMessage(), 2); 

                              oadbtransaction.writeDiagnostics(s,  "WarningToString=" + xxfeEx.toString(), 2); 

                              }

                        }

             

             

                      }

             

             

             

                    opptysearchmanager.setViewQuery(asnopptyqryamimpl, adictionary, arraylist, hashmap1, hashmap2, flag1);

                   

                    OAViewObjectImpl oaviewobjectimpl = (OAViewObjectImpl)findViewObject("OpptySearchVO1");

             

                 String queryOrig = oaviewobjectimpl.getQuery();

             

             

                  queryOrig = queryOrig.replaceAll("hcp.City as City","hp.attribute4 as City");

                  queryOrig = queryOrig.replaceAll("hcp.Province as Province",

                  "(case WHEN length(OpportunityEO.attribute14) != 19 THEN null ELSE fnd_date.canonical_to_date(OpportunityEO.attribute14) END) as Province"

                       );

                  queryOrig = queryOrig.replaceAll("ftt.territory_short_name as Country",

                  "(case WHEN length(OpportunityEO.attribute13) != 19 THEN null ELSE fnd_date.canonical_to_date(OpportunityEO.attribute13) END) as Country"

                       );

             

             

            queryOrig = queryOrig.replaceAll("hcp.state as State",

                  "OpportunityEO.attribute1 as State");

             

                int     var_num      = 0;

                 boolean chngOperator = false;

                 String  dummyStr     = "";

             

             

             

                // opportunity number bind variable declared

                if (queryOrig.indexOf("OpportunityEO.Lead_Number  like :") > -1)

                {

                  if(flag)

                  oadbtransaction.writeDiagnostics(s, "replace oppty_number like to equal statement.", 2);

                  // replace like to =

                  queryOrig = queryOrig.replaceAll("OpportunityEO.Lead_Number  like :","OpportunityEO.Lead_Number  = :");

             

             

                  // getting bind variable index so later we can delete % symbol from it

                  dummyStr =  queryOrig.substring(

                                        queryOrig.indexOf("OpportunityEO.Lead_Number  = :")+"OpportunityEO.Lead_Number  = :".length(),queryOrig.length()

                        );

             

             

                  if(flag)

                     oadbtransaction.writeDiagnostics(s, "xxfe source string1:" + dummyStr , 2);

                  // get next empty symbol (if exist)

                  var_num = dummyStr.indexOf(" ");    

             

             

                  if (var_num  == -1)

                  {

                  var_num  =0;

                  }

                  else

                  {

                    var_num = var_num-1;

                  }

             

             

                  try

                  {

             

             

                    if(flag)

                     oadbtransaction.writeDiagnostics(s, "xxfe source string2:" + dummyStr.substring(0,var_num +1), 2);

                 

                     var_num  = Integer.parseInt(dummyStr.substring(0,var_num +1));

                     if(flag)

                     oadbtransaction.writeDiagnostics(s, "xxfe Variable position is:" + var_num, 2);       

                     chngOperator = true;

             

             

                     Object params_1[] = oaviewobjectimpl.getWhereClauseParams();

                     int bindCount = params_1.length;

                     if (params_1 != null && params_1.length > 0)

                     {

                        for (i=0;i<params_1.length;i++)

                          {

                            if(flag)     

                            oadbtransaction.writeDiagnostics(s, "xxfe param ("+i+")="+ params_1[i], 2);

             

             

                            if (chngOperator && i == var_num)

                            {

                              if(flag)     

                              oadbtransaction.writeDiagnostics(s, "xxfe replacing % symbol in param num=" + i + " val= " + params_1[i].toString(), 2);

                              // replace symbol % in param to emptyness

                              params_1[i] = params_1[i].toString().replaceAll("%","");

                              //set new params

                              oaviewobjectimpl.setWhereClauseParams(params_1);

                              break;

                            }

                          }     

                     }

                  }

                  catch (Exception myEx1)

                  {

                    if(flag)

                    {

                    oadbtransaction.writeDiagnostics(s, "xxfe ERROR! failed to convert oppty number parameter position to integer.", 2);

                    oadbtransaction.writeDiagnostics(s, "xxfe ERROR! Set back = operator to like.", 2);

                    }

                    queryOrig = queryOrig.replaceAll("OpportunityEO.Lead_Number  = :","OpportunityEO.Lead_Number  like :");

                  }

                     

                }

             

             

             

             

                // change category code

              if (xxfeStatusCategoryNull)

              {   

                try

                {

               

                if (queryOrig.indexOf("asb.opp_open_status_flag =  :") > -1)

                {

                  int len1 = 0;

                  int len2 = 0;

                // getting bind variable index so we can replace it

                  dummyStr =  queryOrig.substring(

                                        queryOrig.indexOf("asb.opp_open_status_flag =  :")+"asb.opp_open_status_flag =  :".length(),queryOrig.length()

                        );

             

             

                  //if(flag)

                  //   oadbtransaction.writeDiagnostics(s, "xxfe (change status) left source string:" + dummyStr , 2);

                  // get next empty symbol (if exist)

                  var_num = dummyStr.indexOf(" ");    

             

             

                  if (var_num  == -1)

                  {

                  var_num  =0;

                  }

                  else

                  {

                    var_num = var_num-1;

                  }

                   String var_char = dummyStr.substring(0,var_num +1);

                   var_num  = Integer.parseInt(dummyStr.substring(0,var_num +1));

             

             

                   if(flag)

                   oadbtransaction.writeDiagnostics(s, "xxfe opp_open_status_flag Variable value = " + var_num, 2);

             

             

            // get Status Category bind value:

                     String sStatusCategoryValue = "";

                     Object params_1[] = oaviewobjectimpl.getWhereClauseParams();

                     int bindCount = params_1.length;

             

             

                     if (params_1 != null && params_1.length > 1)

                     {        

                        for (i=0;i<params_1.length;i++)

                          {

                            if(flag)     

                            oadbtransaction.writeDiagnostics(s, "xxfe (change status) param ("+i+")="+ params_1[i], 2);

                           

                            if (i == var_num)

                            {

                              if(flag)     

                              oadbtransaction.writeDiagnostics(s, "xxfe found Status Category value position (i)=" + i + " val= " + params_1[i].toString(), 2);

                              sStatusCategoryValue = params_1[i].toString();

                              break;

                            }

                          }           

                     }

            // change Status category value to X if bind was set to X (means remove parameter criteria)

                   if (sStatusCategoryValue != null && sStatusCategoryValue.equals(xxfeStatusCategoryVal))

                     {

                     len1 =  queryOrig.indexOf("asb.opp_open_status_flag =  :");

                     len2 = len1 + "asb.opp_open_status_flag =  :".length()+var_char.length();

                  

                     String catCodeStr=queryOrig.substring(len1,len2);

             

             

                     if(flag)

                     oadbtransaction.writeDiagnostics(s, "xxfe catCodeStr = " + catCodeStr, 2);

                     queryOrig = queryOrig.replaceAll(catCodeStr," '" + xxfeStatusCategoryVal + "' = :" + var_char);

                     xxfeStatusCategoryNull = false;

                     }

                  }  

                }

                catch (Exception myEx1)

                  {

                    if(flag)

                    {

                    oadbtransaction.writeDiagnostics(s, "xxfe ERROR! failed to set Oppty open status to null.", 2);      

                    oadbtransaction.writeDiagnostics(s,myEx1.getMessage(),2);

                    }

                  }

              } // end of xxfeStatusCategoryNull if

             

             

             

             

             

                  Object params[] = oaviewobjectimpl.getWhereClauseParams();

             

             

             

             

                  int i2 = params.length;

                  Object params2[] = new Object[i2+1];

                  i=params.length;    

                 

              // check if concern field is not empty  and not equal to '%' (all concerns)

              if (xxfeConcern != null && !"".equals(xxfeConcern) && !"%".equals(xxfeConcern))

               {       

                  if (params != null && params.length > 0)

                  {

                   if(flag)

                    oadbtransaction.writeDiagnostics(s, "xxfe init second array with size="+params2.length , 2);

             

             

                    for (i=0;i<params.length;i++)

                  {

                    if(flag)     

                    oadbtransaction.writeDiagnostics(s, "xxfe param ("+i+")="+ params[i], 2);       

                    params2[i]=params[i];

                  }     

              

                    if(flag)

                    oadbtransaction.writeDiagnostics(s, "xxfe concern is not null set value="+xxfeConcern+" to the array2 last inx.",2);

                   

                    params2[params.length] = (Object)xxfeConcern;

             

             

                    if(flag)

                    oadbtransaction.writeDiagnostics(s, "Set where clause params.", 2);

                   

                    oaviewobjectimpl.setWhereClauseParams(params2);

                   

                    // bind variable index

                    i = params2.length-1;

                    String conernValueInx = String.valueOf(i);;

                    String subsLine = "";

                    String relOperator = "=";

             

             

                    // if there is '%' char in string then change operator to LIKE 

                    if (xxfeConcern.indexOf("%")!=-1)

                    {

                        relOperator = " like ";           

                    }       

                    subsLine=" AND upper(hp.attribute4)"+ relOperator +"upper(:"+ conernValueInx +") Order by";       

                    queryOrig = queryOrig.replaceAll("Order by",subsLine);

                    }       

             

             

                // end if, of check if xxfeConcern empty

                }

                  oaviewobjectimpl.setQuery(queryOrig);

               

                // write diagnostic

               boolean customflag1 = oadbtransaction.isLoggingEnabled(2);

                    if(customflag1)

                      {     StringBuffer stringbuffer1 = new StringBuffer(100);

                            stringbuffer1.append("xxfe debug:");

                            stringbuffer1.append("xxfeConcern = ");

                            stringbuffer1.append(xxfeConcern);

                            stringbuffer1.append("criteriaDictMap = ");

                            stringbuffer1.append(hashmap);

                            stringbuffer1.append("renderdVwAttrs = ");

                            stringbuffer1.append(arraylist);

                            stringbuffer1.append("dshBdSrchParams = ");

                            stringbuffer1.append(hashmap1);

                            stringbuffer1.append("miscSrchParams = ");

                            stringbuffer1.append(hashmap2);

                            stringbuffer1.append("defaultSort = ");

                            stringbuffer1.append(boolean1);

                       oadbtransaction.writeDiagnostics(s, stringbuffer1.toString(), 2);       

                       oadbtransaction.writeDiagnostics(s, "xxfe params count="+i, 2);

                       oadbtransaction.writeDiagnostics(s, "xxfe Custom query="+queryOrig,2);

                      } 

                  // set null to the xxfeConcern

                  xxfeConcern=null;

                 

             

                  Object paramsx[] = oaviewobjectimpl.getWhereClauseParams();

             

             

             

             

                  int ix2 = paramsx.length;

                  Object params2x[] = new Object[ix2+1];

                  int ix=paramsx.length;    

                 

              // check if steg field is not empty  and not equal to '%' (all concerns)

              if (xxfeStegVal != null && !"".equals(xxfeStegVal))

               {       

                  if (paramsx != null && paramsx.length > 0)

                  {

                   if(flag)

                    oadbtransaction.writeDiagnostics(s, "Setting where clause for Steg : xxfe init second array with size="+params2x.length , 2);

             

             

                    for (ix=0;ix<paramsx.length;ix++)

                  {

                    if(flag)     

                    oadbtransaction.writeDiagnostics(s, "xxfe paramx ("+ix+")="+ paramsx[ix], 2);       

                    params2x[ix]=paramsx[ix];

                  }     

              

                    if(flag)

                    oadbtransaction.writeDiagnostics(s, "xxfe Steg is not null set value="+xxfeStegVal+" to the array2 last inx.",2);

                   

                    params2x[paramsx.length] = (Object)xxfeStegVal;

             

             

                    if(flag)

                    oadbtransaction.writeDiagnostics(s, "Set where clause params.", 2);

                   

                    oaviewobjectimpl.setWhereClauseParams(params2x);

                   

                    // bind variable index

                    ix = params2x.length-1;

                    String stegValueInx = String.valueOf(ix);;

                    String subsLinex = "";

                    String relOperatorx = "=";

                 

                    subsLinex=" AND upper(OpportunityEO.attribute1)"+ relOperatorx +"upper(:"+ stegValueInx +") Order by";       

                    queryOrig = queryOrig.replaceAll("Order by",subsLinex);

                    }       

             

             

                // end if, of check if xxfeStegVal empty

                }

                  oaviewobjectimpl.setQuery(queryOrig);

               

                // write diagnostic

               boolean customflag1x = oadbtransaction.isLoggingEnabled(2);

                    if(customflag1)

                      {     StringBuffer stringbuffer1 = new StringBuffer(100);

                            stringbuffer1.append("xxfe debug:");

                            stringbuffer1.append("xxfeStegVal = ");

                            stringbuffer1.append(xxfeStegVal);

                            stringbuffer1.append("criteriaDictMap = ");

                            stringbuffer1.append(hashmap);

                            stringbuffer1.append("renderdVwAttrs = ");

                            stringbuffer1.append(arraylist);

                            stringbuffer1.append("dshBdSrchParams = ");

                            stringbuffer1.append(hashmap1);

                            stringbuffer1.append("miscSrchParams = ");

                            stringbuffer1.append(hashmap2);

                            stringbuffer1.append("defaultSort = ");

                            stringbuffer1.append(boolean1);

                       oadbtransaction.writeDiagnostics(s, stringbuffer1.toString(), 2);       

                       oadbtransaction.writeDiagnostics(s, "xxfe params count="+i, 2);

                       oadbtransaction.writeDiagnostics(s, "xxfe Custom query="+queryOrig,2);

                      } 

                  // set null to the xxfeStegVal

                  xxfeStegVal=null;

             

             

             

                    if(!oaviewobjectimpl.isPreparedForExecution())

                        oaviewobjectimpl.executeQuery();

                    if(flag)

                        oadbtransaction.writeDiagnostics(s, "End", 2);

                       

                  //super.initOpptyQuery(hashmap, arraylist, hashmap1, hashmap2,  boolean1);

                }

            • 3. Re: Invalid column index: JBO-27122: SQL error during statement preparation
              aJohny

              Anupam,

               

              Its hard to understand the code as there lots of replacement to the query is happening. Not really sure why this much code is written.

              Since this is the AM code, what all extensions have been done on this page ?

               

              The error is coming in the viewObject executeQuery method. Is the executeQuery() method overridden  ? If Yes, can you provide that code ?

               

              If not, try to print the query, dynamic whereclause, whereclause parameters, just before calling the executeQuery() and see what are the values coming. This will help for further debugging.

               

              From the query in the error message there is only one bind variable, is there anywhere in the code it is trying to set multiple bind parameters ?

               

              Cheers

              AJ

              • 4. Re: Invalid column index: JBO-27122: SQL error during statement preparation
                2725480

                Thanks a lot AJ!

                 

                I could figure out the problem when I tried to print the query, dynamic whereclause, whereclause parameters, just before calling the executeQuery().

                 

                Regards,

                Anupam