10 Replies Latest reply on Feb 9, 2011 11:50 AM by Braj Pratap

    how to add where clause in LOV..?

    784264
    784264 Feb 9, 2011 10:25 AM
      hi

      I want to set the value dynamicaaly to the LOV VO.
      But i am getting errors like

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

      Invalid column type

      Please Help me on this?
      • 605 Views
      • Tags:
        • 1. Re: how to add where clause in LOV..?
          Braj Pratap
          Braj Pratap Feb 9, 2011 10:33 AM (in response to 784264)
          Hi

          Please share complete error stack .

          thanks
          Pratap
          • 2. Re: how to add where clause in LOV..?
            784264
            784264 Feb 9, 2011 10:39 AM (in response to Braj Pratap)
            hi Braj
            thanx for reply..

            this is the error message


            Logout

            Error Page
            Exception Details.
            oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT organization_id,name
            FROM hr_organization_units
            where COST_ALLOCATION_KEYFLEX_ID in
            (SELECT pcak.COST_ALLOCATION_KEYFLEX_ID FROM pay_cost_allocation_keyflex pcak WHERE pcak.segment2 like :1)
                 at oracle.apps.fnd.framework.OAException.wrapperException(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageErrorHandler.processErrors(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
                 at OA.jspService(_OA.java:71)
                 at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
                 at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
                 at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
                 at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
                 at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
                 at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
                 at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
                 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
                 at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
                 at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
                 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
                 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
                 at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
                 at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
                 at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
                 at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
                 at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
                 at java.lang.Thread.run(Thread.java:595)
            ## Detail 0 ##
            java.sql.SQLException: Missing IN or OUT parameter at index:: 1
                 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
                 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
                 at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1566)
                 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2996)
                 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3043)
                 at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860)
                 at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
                 at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3723)
                 at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(Unknown Source)
                 at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:743)
                 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:892)
                 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:806)
                 at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:800)
                 at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3643)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(Unknown Source)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequestAfterController(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
                 at OA.jspService(_OA.java:71)
                 at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
                 at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
                 at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
                 at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
                 at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
                 at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
                 at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
                 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
                 at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
                 at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
                 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
                 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
                 at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
                 at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
                 at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
                 at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
                 at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
                 at java.lang.Thread.run(Thread.java:595)
            java.sql.SQLException: Missing IN or OUT parameter at index:: 1
                 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
                 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
                 at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1566)
                 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2996)
                 at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3043)
                 at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:860)
                 at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:669)
                 at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3723)
                 at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(Unknown Source)
                 at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:743)
                 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:892)
                 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:806)
                 at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:800)
                 at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3643)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(Unknown Source)
                 at oracle.apps.fnd.framework.server.OAViewObjectImpl.initQuery(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.setCriteriaOnVO(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequestAfterController(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAListOfValuesHelper.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.beans.layout.OAListOfValuesBean.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
                 at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(Unknown Source)
                 at OA.jspService(_OA.java:71)
                 at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
                 at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:462)
                 at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)
                 at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:518)
                 at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
                 at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
                 at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
                 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
                 at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
                 at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
                 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
                 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
                 at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
                 at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
                 at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
                 at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
                 at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
                 at java.lang.Thread.run(Thread.java:595)
            • 3. Re: how to add where clause in LOV..?
              Gyan Darpan
              Gyan Darpan Feb 9, 2011 10:41 AM (in response to 784264)
              Hi,

              For this you need to create a Controller (CO) for that LOV.

              And in this CO And in ther PR, you need to add the dynamic where clause.

              Regards,
              Gyan
              • 4. Re: how to add where clause in LOV..?
                784264
                784264 Feb 9, 2011 10:43 AM (in response to 784264)
                hi

                This is my LOV EVENT Code for your reference..




                if(lovBean1 != null)
                {
                if("lovEvent".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM))||"lovPrepare".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM) )
                {

                System.out.println("i m in LovEvent RCDES ");

                String org11= pageContext.getParameter("RcDesc");



                OAViewObjectImpl vo= (OAViewObjectImpl)am.findViewObject("MASOrganizationLOVVO1");


                if(vo!=null)
                {
                vo.setWhereClause(null);
                vo.setWhereClauseParams(null);

                //vo.setWhereClause("COST_ALLOCATION_KEYFLEX_ID LIKE "+"'"+rc+"'");
                vo.setWhereClauseParam(0,rc);
                //vo.executeQuery();


                }}
                • 5. Re: how to add where clause in LOV..?
                  784264
                  784264 Feb 9, 2011 10:44 AM (in response to Gyan Darpan)
                  HI Gyan,

                  Thanx for reply..

                  Its Custom page.


                  i write It in in PFR of CO
                  • 6. Re: how to add where clause in LOV..?
                    Gyan Darpan
                    Gyan Darpan Feb 9, 2011 11:05 AM (in response to 784264)
                    Hi,

                    Step 1 - You need to attach a CO for your LOV.
                    Step 2 - In the PR of this CO you need to set where clause.

                    Hope you know how to add controller to LOV. IF not the let me know.

                    Regards,
                    Gyan

                    Edited by: Gyan on Feb 9, 2011 4:33 PM
                    • 7. Re: how to add where clause in LOV..?
                      784264
                      784264 Feb 9, 2011 10:50 AM (in response to Gyan Darpan)
                      hi Gyan

                      this is my mail id:

                      khushal.kul511@gmail.com
                      • 8. Re: how to add where clause in LOV..?
                        Braj Pratap
                        Braj Pratap Feb 9, 2011 11:42 AM (in response to 784264)
                        hi

                        use


                        String where_clause="COST_ALLOCATION_KEYFLEX_ID LIKE ' %:1%' " ;
                        vo.setWhereClause( where_clause);
                        vo.setWhereClauseParam(0,rc);


                        thanks
                        Pratap
                        • 9. Re: how to add where clause in LOV..?
                          784264
                          784264 Feb 9, 2011 11:21 AM (in response to Braj Pratap)
                          hi Braj,

                          i tried what u suggest earlier But i got COST_ALLOCATION_KEYFLEX_ID is invalid identifier ..


                          This is my LOVVO..

                          SELECT organization_id,name
                          FROM hr_organization_units hou
                          WHERE hou.COST_ALLOCATION_KEYFLEX_ID in(SELECT pcak.COST_ALLOCATION_KEYFLEX_ID FROM pay_cost_allocation_keyflex pcak WHERE pcak.segment2 like :1);


                          Please prepare whereclause for me..
                          i did but i gor error.


                          Thanx
                          • 10. Re: how to add where clause in LOV..?
                            Braj Pratap
                            Braj Pratap Feb 9, 2011 11:50 AM (in response to 784264)
                            Hi

                            if your VO is like this exactly then u r doing in wrong way ,firt you need to modift the query like this



                            SELECT organization_id,name, hou.COST_ALLOCATION_KEYFLEX_ID as COST_ALLOCATION_KEYFLEX_ID
                            FROM hr_organization_units hou
                            WHERE hou.COST_ALLOCATION_KEYFLEX_ID in(SELECT pcak.COST_ALLOCATION_KEYFLEX_ID FROM pay_cost_allocation_keyflex pcak WHERE pcak.segment2 like :1);


                            then set the where clause exactly which i mentioned in previous post .Actually when ever we put the dyanmic where clause ,it always get set on result columns ,and the query that u were using was giving only organization_id in result columns so it was throwing invalid identifier.hope it will help.


                            thanks
                            Pratap