8 Replies Latest reply on Jul 15, 2011 8:29 AM by 868733

    Missing IN or OUT parameter at Index1

    868733
      hi All,

      i write the below code for searching by hardcoding,but i got an exception when i run my page.
      this method i called in the ProcessRequest() method

      public void passTocusttrxn()
      {

      XxcurtradeconsummaryVOImpl cdrillVO=getXxcurtradeconsummaryVO1();

      String s1="CUSTOMER_ID='"+"GX3000"+"'";

      cdrillVO.setWhereClauseParams(null);
      cdrillVO.setWhereClause(s1);
      cdrillVO.executeQuery();

      System.out.println("The executed query is :"+cdrillVO.getQuery());
      }

      Exception Details:
      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (select
      CONSOLIDATION_SUMMARY_ID
      ,CREATION_DATE
      ,CREATED_BY
      ,BATCH_ID
      ,PROCESS_FLAG
      ,ERROR_MESSAGE
      ,CUSTOMER_ID
      ,BILLING_FREQUENCY
      ,CONSOLIDATION_LEVEL
      ,SOLD_TO_ORG_ID
      ,SHIP_TO_ORG_ID
      ,TRANSACTIONAL_CURR_CODE
      ,INVOICE_TO_ORG_ID
      ,PRICE_LIST_ID
      ,ORIG_SYS_DOCUMENT_REF
      ,INVENTORY_ITEM_ID
      ,ORGANIZATION_ID
      ,ORDERED_ITEM
      ,ORDERED_QUANTITY
      ,UNIT_LIST_PRICE
      ,UNIT_SEELING_PRICE
      ,ORDER_QUANTITY_UOM
      ,ORDER_NUMBER
      ,ORDER_DATE
      ,INVOICE_NUMBER
      ,INVOICE_DATE
      ,FEE_CALCULATION_WORKSHEET_PDF
      ,TRADE_DETAILS_PDF
      ,INVOICE_REPORT_PDF
      ,PRICING_ATTRIBUTE_CONTEXT
      ,PRICING_ATTRIBUTE
      ,PRICING_ATTR_VALUE_FROM
      from xxcur_trade_consol_summary xxcrnx
      where xxcrnx.customer_id=nvl(:1,xxcrnx.customer_id)) QRSLT WHERE (CUSTOMER_ID='GX3000')
           at oracle.apps.fnd.framework.OAException.wrapperException(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(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 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:857)
           at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:666)
           at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3655)
           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:742)
           at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:891)
           at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:805)
           at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:799)
           at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3575)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(Unknown Source)
           at xxcurnx.oracle.apps.ont.custdtls.server.XxcustdtlsmainAMImpl.passTocusttrxn(XxcustdtlsmainAMImpl.java:166)
           at xxcurnx.oracle.apps.ont.custdtls.webui.XxcusttransactionCO.processRequest(XxcusttransactionCO.java:33)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(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 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:857)
           at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:666)
           at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3655)
           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:742)
           at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:891)
           at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:805)
           at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:799)
           at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3575)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(Unknown Source)
           at xxcurnx.oracle.apps.ont.custdtls.server.XxcustdtlsmainAMImpl.passTocusttrxn(XxcustdtlsmainAMImpl.java:166)
           at xxcurnx.oracle.apps.ont.custdtls.webui.XxcusttransactionCO.processRequest(XxcusttransactionCO.java:33)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequestChildren(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.beans.OABodyBean.processRequest(Unknown Source)
           at oracle.apps.fnd.framework.webui.OAPageBean.processRequest(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 com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
           at java.lang.Thread.run(Thread.java:595)

      Thanks,
        • 1. Re: Missing IN or OUT parameter at Index1
          Gyan Darpan
          Hi,

          You are executing the VO without passing the parameter. Although i can see that u have hardcoded the value in where Clause.

          Post me the Query u r getting

          System.out.println("The executed query is :"+cdrillVO.getQuery());

          Regards,
          Gyan
          • 2. Re: Missing IN or OUT parameter at Index1
            868733
            hi Gyan,
            can you please test my query.

            select
            CONSOLIDATION_SUMMARY_ID
            ,CREATION_DATE
            ,CREATED_BY
            ,BATCH_ID
            ,PROCESS_FLAG
            ,ERROR_MESSAGE
            ,CUSTOMER_ID
            ,BILLING_FREQUENCY
            ,CONSOLIDATION_LEVEL
            ,SOLD_TO_ORG_ID
            ,SHIP_TO_ORG_ID
            ,TRANSACTIONAL_CURR_CODE
            ,INVOICE_TO_ORG_ID
            ,PRICE_LIST_ID
            ,ORIG_SYS_DOCUMENT_REF
            ,INVENTORY_ITEM_ID
            ,ORGANIZATION_ID
            ,ORDERED_ITEM
            ,ORDERED_QUANTITY
            ,UNIT_LIST_PRICE
            ,UNIT_SEELING_PRICE
            ,ORDER_QUANTITY_UOM
            ,ORDER_NUMBER
            ,ORDER_DATE
            ,INVOICE_NUMBER
            ,INVOICE_DATE
            ,FEE_CALCULATION_WORKSHEET_PDF
            ,TRADE_DETAILS_PDF
            ,INVOICE_REPORT_PDF
            ,PRICING_ATTRIBUTE_CONTEXT
            ,PRICING_ATTRIBUTE
            ,PRICING_ATTR_VALUE_FROM
            from xxcur_trade_consol_summary xxcrnx
            where xxcrnx.customer_id=nvl(:1,xxcrnx.customer_id)

            i am also tride the below code:

            public void passTocusttrxn(String vCustdrlid)
            {

            XxcurtradeconsummaryVOImpl cdrillVO=getXxcurtradeconsummaryVO1();

            String s1="CUSTOMER_ID='"+vCustdrlid+"'";

            cdrillVO.setWhereClauseParams(null);
            cdrillVO.setWhereClause(s1);
            cdrillVO.executeQuery();

            System.out.println("The executed query is :"+cdrillVO.getQuery());
            }

            Thanks,
            • 3. Re: Missing IN or OUT parameter at Index1
              Gyan Darpan
              Hi,

              If i am clear then ur existing query is

              select
              CONSOLIDATION_SUMMARY_ID,CREATION_DATE,CREATED_BY,BATCH_ID,PROCESS_FLAG,ERROR_MESSAGE,CUSTOMER_ID,BILLING_FREQUENCY
              ,CONSOLIDATION_LEVEL,SOLD_TO_ORG_ID ,SHIP_TO_ORG_ID ,TRANSACTIONAL_CURR_CODE ,INVOICE_TO_ORG_ID ,PRICE_LIST_ID
              ,ORIG_SYS_DOCUMENT_REF,INVENTORY_ITEM_ID ,ORGANIZATION_ID ,ORDERED_ITEM ,ORDERED_QUANTITY ,UNIT_LIST_PRICE
              ,UNIT_SEELING_PRICE,ORDER_QUANTITY_UOM,ORDER_NUMBER ,ORDER_DATE ,INVOICE_NUMBER ,INVOICE_DATE
              ,FEE_CALCULATION_WORKSHEET_PDF ,TRADE_DETAILS_PDF ,INVOICE_REPORT_PDF ,PRICING_ATTRIBUTE_CONTEXT
              ,PRICING_ATTRIBUTE ,PRICING_ATTR_VALUE_FROM
              from xxcur_trade_consol_summary xxcrnx
              where xxcrnx.customer_id=nvl(:1,xxcrnx.customer_id)

              And then ur trying to append another where clause using below code

              String s1="CUSTOMER_ID='"vCustdrlid"'";
              cdrillVO.setWhereClauseParams(null);
              cdrillVO.setWhereClause(s1);

              Which i dnt think is exactly ur require....what u need to pass the parameter to the above Query.

              Rectify me...if i m wrong.

              Regards,
              Gyan
              • 4. Re: Missing IN or OUT parameter at Index1
                872073
                There is a ":1" in your query.

                so you need to pass value for it, using
                setWhereClauseParam(0, <value>);


                But since you you are setting the dynamic where clause, you can remove the :1 from query.
                • 5. Re: Missing IN or OUT parameter at Index1
                  HrishikeshJ
                  All you need to do is

                  public void passTocusttrxn(String vCustdrlid)
                  {
                  XxcurtradeconsummaryVOImpl cdrillVO=getXxcurtradeconsummaryVO1();
                  cdrillVO.setWhereClauseParams(null);
                  cdrillVO.setWhereClauseParam(0, vCustdrlid);
                  //cdrillVO.executeQuery();
                  }


                  Hrishikesh
                  1 person found this helpful
                  • 6. Re: Missing IN or OUT parameter at Index1
                    HaroonAfridi
                    Hi,

                    Try the following


                    public void passTocusttrxn()
                    {

                    //XxcurtradeconsummaryVOImpl cdrillVO=getXxcurtradeconsummaryVO1();
                    OAViewObject cdrillVO=getXxcurtradeconsummaryVO1();

                    String ID = "'GX3000'";
                    String s1="CUSTOMER_ID="+ID;

                    cdrillVO.setWhereClause(s1);
                    cdrillVO.executeQuery();

                    System.out.println("The executed query is :"+cdrillVO.getQuery());
                    }


                    Note: where xxcrnx.customer_id=nvl(:1*,xxcrnx.customer_id)) QRSLT WHERE (CUSTOMER_ID='GX3000')*, one of the possible cause of this exception is *:1*
                    Haroon

                    Edited by: Haroon Afridi on Jul 14, 2011 9:53 AM
                    1 person found this helpful
                    • 7. Re: Missing IN or OUT parameter at Index1
                      MeherIrk
                      Use below one:

                      String s1="CUSTOMER_ID='"GX3000"' ";

                      Regards
                      Meher Irk
                      1 person found this helpful
                      • 8. Re: Missing IN or OUT parameter at Index1
                        868733
                        hi Gyan,

                        The problem is with the where clause only,that i can pass dynamically
                        String s1="CUSTOMER_ID='"+vCustdrlid+"'";

                        but in query
                        where xxcrnx.customer_id=nvl(:1,xxcrnx.customer_id)
                        this we need to be delete or we change the Where clause like

                        cdrillVO.setWhereClauseParams(null);
                        cdrillVO.setWhereClauseParam(0,vCustdrlid);
                        cdrillVO.executeQuery();

                        Regards,