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

Create new look up data using List of Values (LOVs) in Oracle ADF

While working on project , I have seen such type of LOV that have a option of create Lov Value at selection time, this is quite good, as if desired value is not available in list then user can create on that time.

This type of list looks like this

It means if there is no company available in list you need not to go on Company form, user can create it directly from here.
Doing this in ADF is quite simple , in this tutorial i am taking example of Oracle default HR schema (Employees and Departments) table-
Implementation Steps-

• Create business components from Department and Employees table 





•  Now create LOV on DepartmentId of employees ViewObject from Department VO

• Go to UI Hints tab of LOV and select Combo Box with List of Values

•  Now drag fields of Employee from DataControl to page as form

•  Select Department Id Lov and go to structure window, and drop a link in customActions facet of  af:inputComboboxListOfValues

• Now drag a popup on page and drag Department VO on af:dialog as form to create new department. and other things are same as normal form
  

• Bind this popup to bean and create a ActionListener on the link inside facet to invoke createInsert of department table



package lookup.view.bean;

import java.io.Serializable;

import javax.faces.context.FacesContext;
import javax.faces.event.ActionEvent;

import oracle.adf.model.BindingContext;

import oracle.adf.view.rich.component.rich.RichPopup;

import oracle.adf.view.rich.event.DialogEvent;

import oracle.binding.BindingContainer;
import oracle.binding.OperationBinding;

import org.apache.myfaces.trinidad.render.ExtendedRenderKitService;
import org.apache.myfaces.trinidad.util.Service;

public class LookupDataBean implements Serializable {
    private RichPopup deptPopUpBind;

    public LookupDataBean() {
    }

    public BindingContainer getBindings() {
        return BindingContext.getCurrent().getCurrentBindingsEntry();
    }

    private void showPopup(RichPopup pop, boolean visible) {
        try {
            FacesContext context = FacesContext.getCurrentInstance();
            if (context != null && pop != null) {
                String popupId = pop.getClientId(context);
                if (popupId != null) {
                    StringBuilder script = new StringBuilder();
                    script.append("var popup = AdfPage.PAGE.findComponent('").append(popupId).append("'); ");
                    if (visible) {
                        script.append("if (!popup.isPopupVisible()) { ").append("popup.show();}");
                    } else {
                        script.append("if (popup.isPopupVisible()) { ").append("popup.hide();}");
                    }
                    ExtendedRenderKitService erks =
                        Service.getService(context.getRenderKit(), ExtendedRenderKitService.class);
                    erks.addScript(context, script.toString());
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void createDept(ActionEvent actionEvent) {
        BindingContainer bindings = getBindings();
        OperationBinding ob = bindings.getOperationBinding("CreateInsert");
        ob.execute();
        showPopup(deptPopUpBind, true);
    }

    public void DeptDialogListener(DialogEvent dialogEvent) {
        FacesContext fct = FacesContext.getCurrentInstance();
        if (dialogEvent.getOutcome().name().equals("ok")) {
            BindingContainer bindings = getBindings();
            OperationBinding ob = bindings.getOperationBinding("Commit");
            ob.execute();

        }
    }

    public void setDeptPopUpBind(RichPopup deptPopUpBind) {
        this.deptPopUpBind = deptPopUpBind;
    }

    public RichPopup getDeptPopUpBind() {
        return deptPopUpBind;
    }
}



• Now run this application and click on Lov of DepartmentId, you will see your link there to create Department

• Now see that how many departments are listed currently in LOV

• Now click on Add Department link and add a department

•  Again see the listed departments- Oracle ADF Tutorial is added in now list

 this is how you can use this beautiful feature in your LOV-
Sample ADF Application- Download