JBO-27022-Failed to load value at index 'n' , java.sql.SQLException: Invalid column index in Oracle ADF

'Invalid column index' is the error occurs due to SQL Exception in ADF Faces.
and main reason behind this is mismatch between ViewObject's query and Attributes.
 suppose i have an application with default HR Schema and Employees Table.

• See the query and attribute list of Employees ViewObject , when there is no mismatch between attributes and query

 

and query is -

 

• Now i have removed a attribute from ViewObject query, then mismatch between VO Query and attribute list tends towards sql exception "Invalid Column Index"

 Query is- (First_Name is not there)

•  Now when you try to run application, this error occurs, "Failed to load value at index n - java.sql.SQLException Invalid column index"- see error log 



oracle.jbo.AttributeLoadException: JBO-27022: Failed to load value at index 11 with java object of type java.lang.String due to java.sql.SQLException.
 at oracle.jbo.server.AttributeDefImpl.loadFromResultSet(AttributeDefImpl.java:2435)
 at oracle.jbo.server.ViewRowImpl.populate(ViewRowImpl.java:3842)
 at oracle.jbo.server.ViewDefImpl.createInstanceFromResultSet(ViewDefImpl.java:2387)
 at oracle.jbo.server.ViewObjectImpl.createRowFromResultSet(ViewObjectImpl.java:6030)
 at oracle.jbo.server.ViewObjectImpl.createInstanceFromResultSet(ViewObjectImpl.java:5861)
 at oracle.jbo.server.QueryCollection.populateRow(QueryCollection.java:3612)
 at oracle.jbo.server.QueryCollection.fetch(QueryCollection.java:3467)
 at oracle.jbo.server.QueryCollection.get(QueryCollection.java:2184)
 at oracle.jbo.server.ViewRowSetImpl.getRow(ViewRowSetImpl.java:5125)
 at oracle.jbo.server.ViewRowSetIteratorImpl.doFetch(ViewRowSetIteratorImpl.java:2936)
 at oracle.jbo.server.ViewRowSetIteratorImpl.ensureRefreshed(ViewRowSetIteratorImpl.java:2792)
 at oracle.jbo.server.ViewRowSetIteratorImpl.refresh(ViewRowSetIteratorImpl.java:3033)
 at oracle.jbo.server.ViewRowSetImpl.notifyRefresh(ViewRowSetImpl.java:2797)
 at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:1255)
 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:1413)
 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:1319)
 at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:1304)
 at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:7119)
 at oracle.adf.model.bc4j.DCJboDataControl.executeIteratorBindingIfNeeded(DCJboDataControl.java:1366)
 at oracle.adf.model.binding.DCIteratorBinding.executeQueryIfNeeded(DCIteratorBinding.java:2219)
 at oracle.jbo.uicli.binding.JUCtrlHierBinding.getRootNodeBinding(JUCtrlHierBinding.java:92)
 at oracle.adfinternal.view.faces.model.binding.FacesCtrlHierBinding$HierCurrencyRowKeySet.getRowIterator(FacesCtrlHierBinding.java:1421)
 at oracle.adfinternal.view.faces.model.binding.CurrencyRowKeySet.size(CurrencyRowKeySet.java:34)
 at org.apache.myfaces.trinidad.model.RowKeySet.getSize(RowKeySet.java:152)
 at oracle.adfinternal.view.faces.renderkit.rich.TableRendererUtils.writePojoSelectionState(TableRendererUtils.java:455)
 at oracle.adfinternal.view.faces.renderkit.rich.TableRenderer.renderDataBlockRows(TableRenderer.java:2562)
 at oracle.adfinternal.view.faces.renderkit.rich.TableRenderer.encodeAll(TableRenderer.java:685)
 at oracle.adf.view.rich.render.RichRenderer.encodeAll(RichRenderer.java:1452)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeEnd(CoreRenderer.java:511)
 at org.apache.myfaces.trinidad.component.UIXComponentBase.encodeEnd(UIXComponentBase.java:923)
 at org.apache.myfaces.trinidad.component.UIXCollection.encodeEnd(UIXCollection.java:617)
 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1659)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeChild(CoreRenderer.java:624)
 at oracle.adf.view.rich.render.RichRenderer.encodeChild(RichRenderer.java:3201)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeAllChildren(CoreRenderer.java:641)
 at oracle.adf.view.rich.render.RichRenderer.encodeAllChildrenInContext(RichRenderer.java:3062)
 at oracle.adfinternal.view.faces.renderkit.rich.FormRenderer.encodeAll(FormRenderer.java:274)
 at oracle.adf.view.rich.render.RichRenderer.encodeAll(RichRenderer.java:1452)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeEnd(CoreRenderer.java:511)
 at org.apache.myfaces.trinidad.component.UIXComponentBase.encodeEnd(UIXComponentBase.java:923)
 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1659)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeChild(CoreRenderer.java:624)
 at oracle.adf.view.rich.render.RichRenderer.encodeChild(RichRenderer.java:3201)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeAllChildren(CoreRenderer.java:641)
 at oracle.adf.view.rich.render.RichRenderer.encodeAllChildrenInContext(RichRenderer.java:3062)
 at oracle.adfinternal.view.faces.renderkit.rich.DocumentRenderer.encodeAll(DocumentRenderer.java:1277)
 at oracle.adf.view.rich.render.RichRenderer.encodeAll(RichRenderer.java:1452)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeEnd(CoreRenderer.java:511)
 at org.apache.myfaces.trinidad.component.UIXComponentBase.encodeEnd(UIXComponentBase.java:923)
 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1659)
 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1655)
 at oracle.adfinternal.view.faces.component.AdfViewRoot.encodeAll(AdfViewRoot.java:91)
 at com.sun.faces.application.view.JspViewHandlingStrategy.doRenderView(JspViewHandlingStrategy.java:431)
 at com.sun.faces.application.view.JspViewHandlingStrategy.renderView(JspViewHandlingStrategy.java:233)
 at org.apache.myfaces.trinidadinternal.application.ViewDeclarationLanguageFactoryImpl$ChangeApplyingVDLWrapper.renderView(ViewDeclarationLanguageFactoryImpl.java:350)
 at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:131)
 at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:273)
 at org.apache.myfaces.trinidadinternal.application.ViewHandlerImpl.renderView(ViewHandlerImpl.java:165)
 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._renderResponse(LifecycleImpl.java:1027)
 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._executePhase(LifecycleImpl.java:334)
 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:232)
 at javax.faces.webapp.FacesServlet.service(FacesServlet.java:313)
 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:300)
 at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.adf.model.servlet.ADFBindingFilter.doFilter(ADFBindingFilter.java:173)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.adfinternal.view.faces.webapp.rich.RegistrationFilter.doFilter(RegistrationFilter.java:122)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(TrinidadFilterImpl.java:468)
 at oracle.adfinternal.view.faces.activedata.AdsFilter.doFilter(AdsFilter.java:60)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(TrinidadFilterImpl.java:468)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl._doFilterImpl(TrinidadFilterImpl.java:293)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl.doFilter(TrinidadFilterImpl.java:199)
 at org.apache.myfaces.trinidad.webapp.TrinidadFilter.doFilter(TrinidadFilter.java:92)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:111)
 at java.security.AccessController.doPrivileged(Native Method)
 at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:313)
 at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:413)
 at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:94)
 at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:161)
 at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:136)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at weblogic.servlet.internal.RequestEventsFilter.doFilter(RequestEventsFilter.java:27)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3715)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3681)
 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:2277)
 at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2183)
 at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1454)
 at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
 at weblogic.work.ExecuteThread.run(ExecuteThread.java:178)
Caused by: java.sql.SQLException: Invalid column index
 at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:2824)
 at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImpl.getString(Unknown Source)
 at oracle.jbo.common.StringTypeSQLNativeImpl.getDataFromResultSet(JboTypeMapEntries.java:509)
 at oracle.jbo.server.AttributeDefImpl.loadFromResultSet(AttributeDefImpl.java:2426)
 ... 97 more
## Detail 0 ## 

• So when you see this type of log , first check your viewObjects and their query.
• Always match attribute order in query of view object and in XML source, if there is mismatch in order, it may result in  great problem or data mistake

        See here is difference in order of attribute of vo and query- (Last_Name and Email)

• In this case of mismatch in order, two things may happen-
• First one- if Data type of mismatched attributes matches (as Last_Name and Email both are Var char) then value in both fields will be set in VO otherwise it will throw an exception.
• See this scenario in given snap- Only due to wrong ordering ,value of Last_Name and Email got replaced

• And if you have Impl classes for your Entity Object or View Object then also check indexing in  classes ,query and  XML source.
• Invalid Column Index may also occur due to pl/sql (database) function call, always check it and its parameter indexing