7 Replies Latest reply: Feb 14, 2013 6:28 AM by 990534 RSS

    Missing in out params while setting where clause bind variable

    RajaSekharReddy.M
      Hi all,
      I am using Jdev 11.1.6

      I have a 3 level master - child hirearchy .

      I want to apply filter using where clause at the third level . I have used the following post of frank [http://www.oracle.com/technetwork/developer-tools/adf/learnmore/feb2011-otn-harvest-328207.pdf] .

      But i am getting an exception that the bind variable is missing for the relation i.e, view link bind variable.

      Edited by: RajaSekharReddy.M on Feb 11, 2013 4:43 AM
        • 1. Re: Missing in out params while setting where clause bind variable
          Frank Nimphius-Oracle
          Hi,

          see here: https://blogs.oracle.com/jdevotnharvest/entry/the_infamous_missing_in_or

          Frank
          • 2. Re: Missing in out params while setting where clause bind variable
            990534
            Hi Frank,

            Thanks for the link have tried using the view criteria with optional bind variable again.

            Still i am getting the same issue . While debugging with model debugger i don't see any exception as such. But i see two variables being set there in the model debugger

            1) Bind variable i use (*EmployeeId*)

            2) View link bind variable (*Bind_Fieldid*)

            But when i am trying to set the view link bind variable it's throwing bind variable not found exception.

            *[oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.  Statement: SELECT Reviewdata.FIELDID,         Reviewdata.EMPLOYEEID,         Reviewdata.FIELDDATA,         Reviewdata.RATING1DATA,         Reviewdata.RATING2DATA,         Reviewdata.ROWID FROM REVIEWDATA Reviewdata WHERE ( ( (Reviewdata.EMPLOYEEID = :EmployeeId ) ) ) AND Reviewdata.FIELDID = :Bind_Fieldid]*

            This is my stack trace

            java.sql.SQLException: Missing IN or OUT parameter at index:: 2

                 at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1899)

                 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3764)

                 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)

                 at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)

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

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

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

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

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

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

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

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

                 at model.AppModule_employeeImpl.filterEmployees(AppModule_employeeImpl.java:66)

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

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

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

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

                 at oracle.adf.model.binding.DCInvokeMethod.invokeMethod(DCInvokeMethod.java:657)

                 at oracle.adf.model.binding.DCDataControl.invokeMethod(DCDataControl.java:2143)

                 at oracle.adf.model.bc4j.DCJboDataControl.invokeMethod(DCJboDataControl.java:3114)

                 at oracle.adf.model.binding.DCInvokeMethod.callMethod(DCInvokeMethod.java:261)

                 at oracle.jbo.uicli.binding.JUCtrlActionBinding.doIt(JUCtrlActionBinding.java:1635)

                 at oracle.adf.model.binding.DCDataControl.invokeOperation(DCDataControl.java:2150)

                 at oracle.jbo.uicli.binding.JUCtrlActionBinding.invoke(JUCtrlActionBinding.java:740)

                 at oracle.adf.controller.v2.lifecycle.PageLifecycleImpl.executeEvent(PageLifecycleImpl.java:402)

                 at oracle.adfinternal.view.faces.model.binding.FacesCtrlActionBinding._execute(FacesCtrlActionBinding.java:252)

                 at oracle.adfinternal.view.faces.model.binding.FacesCtrlActionBinding.execute(FacesCtrlActionBinding.java:210)

                 at view.employee.FormManagerBean.executeOpBindings(FormManagerBean.java:53)

                 at view.employee.FormManagerBean.prepareFormModel(FormManagerBean.java:76)

                 at view.employee.FormManagerBean.testAction(FormManagerBean.java:91)

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

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

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

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

                 at com.sun.el.parser.AstValue.invoke(Unknown Source)

                 at com.sun.el.MethodExpressionImpl.invoke(Unknown Source)

                 at org.apache.myfaces.trinidadinternal.taglib.util.MethodExpressionMethodBinding.invoke(MethodExpressionMethodBinding.java:53)

                 at org.apache.myfaces.trinidad.component.UIXComponentBase.broadcastToMethodBinding(UIXComponentBase.java:1256)

                 at org.apache.myfaces.trinidad.component.UIXCommand.broadcast(UIXCommand.java:183)

                 at oracle.adf.view.rich.component.fragment.UIXRegion.broadcast(UIXRegion.java:148)

                 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.broadcastEvents(LifecycleImpl.java:1018)

                 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._executePhase(LifecycleImpl.java:386)

                 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:194)

                 at javax.faces.webapp.FacesServlet.service(FacesServlet.java:265)

                 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:205)

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

                 at oracle.adfinternal.view.faces.webapp.rich.RegistrationFilter.doFilter(RegistrationFilter.java:106)

                 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(TrinidadFilterImpl.java:446)

                 at oracle.adfinternal.view.faces.activedata.AdsFilter.doFilter(AdsFilter.java:60)

                 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(TrinidadFilterImpl.java:446)

                 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl._doFilterImpl(TrinidadFilterImpl.java:271)

                 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl.doFilter(TrinidadFilterImpl.java:177)

                 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:119)

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

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

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

                 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:56)

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

                 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)

            Edited by: RajaSekharReddy.M on Feb 12, 2013 3:06 AM

            Edited by: RajaSekharReddy.M on Feb 12, 2013 3:07 AM
            • 3. Re: Missing in out params while setting where clause bind variable
              Sudipto Desmukh
              @Rajasekhar ,
              This is an issue even I faced few months back and this doesnot work using the approach mentioned.
              Missing IN or OUT parameters error -filtering treeModel using VL Accessor

              I couldnot make it work and have attached a reproducible test case in the above link. My requirement was to filter the 3rd level or leaf node ... I worked around it by filterring the tree Model in the page Bindings itself in
              the FilterAttribute & FilterValue fields in the leaf nodes.

              I will forward you that email.

              Thanks
              Sudipto
              • 4. Re: Missing in out params while setting where clause bind variable
                Suresh Karunarathne
                Hi,
                Check whether your bind variable(your VO --> Query --> Bind Varibale) is updatable check box is selected,If not select it
                • 5. Re: Missing in out params while setting where clause bind variable
                  990534
                  Hi Suresh,
                  Thanks for the reply . I have my bind variable set as optional and updatable. The issue is i am getting a missing in out param because of the bind variable in VIEW LINK.

                  Thanks,
                  Raj
                  • 6. Re: Missing in out params while setting where clause bind variable
                    990534
                    Hi all,
                    Any help !!

                    Thanks,
                    Raj.
                    • 7. Re: Missing in out params while setting where clause bind variable
                      990534
                      Didn't solve the issue but found a work around . Any suggestions would be welcomed.

                      Here are two solutions i found

                      1)If you want to filter based on a single value (which doesn't change in the application) use the filter attribute in the tree

                      2) Use this link jobinesh has well written the blog thanks Jobinesh for the blog :) [http://jobinesh.blogspot.in/2010/01/search-by-child-attributes-on-tree.html]


                      Want to mark the post the answered but couldnn't do that because of the recent email change of my account made my earlier posts unavailable for me


                      Thanks,

                      Raj