5 Replies Latest reply: Feb 29, 2008 9:57 PM by 557833 RSS

    ORA-01006: bind variable does not exist

    624291
      Hello. I have a link embedded in a table that hits a controller class and forwards to a JSP.
      I'm using the forwardURL method:

      pageContext.setForwardURL("CAOADownload.jsp?transactionid=" + pageContext.getTransactionId() + "&retainAM=Y&PO_LINE_ID=" + row.getAttribute("PO_LINE_ID").toString(),
      null,
      OAWebBeanConstants.KEEP_MENU_CONTEXT,
      null,
      null,
      true,
      OAWebBeanConstants.ADD_BREAD_CRUMB_YES,
      OAWebBeanConstants.IGNORE_MESSAGES);

      Everything works fine at this point. However when I click the 'Show' link on the Purchase Order detail screen I get this stack trace:

      Exception Details.

      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT PLL.LINE_LOCATION_ID,PLL.PO_HEADER_ID,PLL.PO_LINE_ID, PLL.PO_RELEASE_ID,PLL.UNIT_MEAS_LOOKUP_CODE, PLL.PROMISED_DATE PROMISED_DATE, PLL.PRICE_OVERRIDE PRICE_OVERRIDE,PLL.QUANTITY*PLL.PRICE_OVERRIDE Amount, PLL.PRICE_DISCOUNT,PLL.SHIPMENT_NUM,POH.CURRENCY_CODE, PLL.ATTRIBUTE1, PLL.ATTRIBUTE2, PLL.ATTRIBUTE3, PLL.ATTRIBUTE4, PLL.ATTRIBUTE5, PLL.ATTRIBUTE6, PLL.ATTRIBUTE7, PLL.ATTRIBUTE8, PLL.ATTRIBUTE9, PLL.ATTRIBUTE10, PLL.ATTRIBUTE11, PLL.ATTRIBUTE12, PLL.ATTRIBUTE13, PLL.ATTRIBUTE14, PLL.ATTRIBUTE15, PLL.ATTRIBUTE_CATEGORY, PLL.QUANTITY, PLLA.QUANTITY_RECEIVED, PLLA.QUANTITY_BILLED, PLLA.QUANTITY_ACCEPTED, PLLA.QUANTITY_REJECTED,PLLA.QUANTITY_CANCELLED, PLLA.QUANTITY_SHIPPED, PLLA.CLOSED_CODE,PLLA.CLOSED_FLAG,PLLA.CANCEL_FLAG,PLLA.APPROVED_FLAG, PLL.USSGL_TRANSACTION_CODE,PLL.ACCRUE_ON_RECEIPT_FLAG, PLL.FREIGHT_TERMS_LOOKUP_CODE,PLL.FOB_LOOKUP_CODE, PLL.ENCUMBERED_FLAG,PLL.UNENCUMBERED_QUANTITY,PLL.SHIP_VIA_LOOKUP_CODE, PLL.SHIP_TO_LOCATION_ID,POH.REVISION_NUM,PLL.SHIP_TO_ORGANIZATION_ID, POL.LINE_NUM,PLL.SHIPMENT_TYPE,POL.ALLOW_PRICE_OVERRIDE_FLAG, POL.NOT_TO_EXCEED_PRICE,PLL.FROM_HEADER_ID, NVL(HRL.LOCATION_CODE, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY),1,20)) LOCATION_CODE, nvl(POCR.NEW_QUANTITY,PLL.QUANTITY) NEW_QUANTITY,NVL(POCR.NEW_PROMISED_DATE,PLL.PROMISED_DATE) NEW_PROMISED_DATE, POCR.REQUEST_STATUS,NVL(POCR.NEW_PRICE,NVL(POCR.OLD_PRICE,PLL.PRICE_OVERRIDE)) NEW_PRICE,POCR.REQUEST_REASON, NVL(POCR.NEW_PRICE,NVL(POCR.OLD_PRICE,PLL.PRICE_OVERRIDE))*NVL(POCR.NEW_QUANTITY,PLL.QUANTITY) NewAmount, null ShipmentStatus, PLLA.SUPPLIER_ORDER_LINE_NUMBER, nvl(pocr.NEW_SUPPLIER_ORDER_LINE_NUMBER,PLLA.SUPPLIER_ORDER_LINE_NUMBER) NEW_SUPPLIER_ORDER_LINE_NUMBER,PLL.NEED_BY_DATE, PLL.START_DATE,PLL.END_DATE,'DATE_TYPE','QTY_TYPE', 'SUP_ORDER_TYPE','SHIP_TYPE','PRICE_BRK_TYPE',HRO.NAME ORGANIZATION_NAME,'REQUESTOR','REQUESTOR_ID','REQ_TYPE','AMOUNT_FORMATTED', NVL(PLLA.CONSIGNED_FLAG,'N') CONSIGNED_FLAG, 'CONSIGNED', NVL(PLLA.VMI_FLAG,'N') VMI_FLAG, 'VMI','ACTION','ACTION_TYPE','REASON_TYPE', NVL(PLL.DROP_SHIP_FLAG,'N') DROP_SHIP_FLAG,'SPLIT_TYPE', TO_NUMBER(NULL) PARENT_LINE_LOCATION_ID, 'SHIP_PRICE_DIFF_TYPE','ENTITY_TYPE',POLT.ORDER_TYPE_LOOKUP_CODE LINE_TYPE_CODE,POH.GLOBAL_AGREEMENT_FLAG,null JOB_TITLE,null JOB_DESCRIPTION, null IS_SPLIT, POLT.PURCHASE_BASIS LINE_PURCHASE_BASIS,PLL.AMOUNT SHIP_AMOUNT,PLLA.AMOUNT_RECEIVED,PLLA.AMOUNT_BILLED, PLLA.AMOUNT_CANCELLED,PLLA.AMOUNT_REJECTED,PLLA.AMOUNT_ACCEPTED,PLL.SALES_ORDER_UPDATE_DATE,null SPLIT_SHIP, PLL.SOURCE_SHIPMENT_ID, POH.SEGMENT1 PO_NUM, POL.CONTRACTOR_FIRST_NAME || ' ' || POL.CONTRACTOR_LAST_NAME CONTRACTOR_NAME FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL,PO_LINE_LOCATIONS_ALL PLLA, PO_CHANGE_REQUESTS POCR,HR_LOCATIONS_ALL_TL HRL, PO_HEADERS_ALL POH,HZ_LOCATIONS HZ, PO_LOOKUP_CODES POLC, PO_LINES_ALL POL, HR_ALL_ORGANIZATION_UNITS_TL HRO, PO_LINE_TYPES POLT WHERE PLL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID (+) AND PLL.SHIP_TO_LOCATION_ID =HZ.LOCATION_ID (+) AND HRL.LANGUAGE (+) = USERENV('LANG') AND PLL.LATEST_EXTERNAL_FLAG= 'Y' AND POL.LINE_TYPE_ID = POLT.LINE_TYPE_ID AND PLL.LINE_LOCATION_ID = PLLA.LINE_LOCATION_ID AND HRO.ORGANIZATION_ID (+) = PLL.SHIP_TO_ORGANIZATION_ID AND HRO.LANGUAGE (+)= userenv('LANG') AND PLL.PO_LINE_ID = POL.PO_LINE_ID AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID AND POLC.LOOKUP_CODE = NVL(PLL.CLOSED_CODE, 'OPEN') AND POLC.LOOKUP_TYPE = 'DOCUMENT STATE' AND PLL.PO_HEADER_ID = POCR.DOCUMENT_HEADER_ID (+) AND PLL.LINE_LOCATION_ID = POCR.DOCUMENT_LINE_LOCATION_ID (+) AND POCR.REQUEST_LEVEL (+)= 'SHIPMENT' AND POCR.CHANGE_ACTIVE_FLAG (+)= 'Y' UNION SELECT PLL.LINE_LOCATION_ID,PLL.PO_HEADER_ID,PLL.PO_LINE_ID, PLL.PO_RELEASE_ID,PLL.UNIT_MEAS_LOOKUP_CODE, to_date(NULL) PROMISED_DATE, NVL(POCR.OLD_PRICE,PLL.PRICE_OVERRIDE) PRICE_OVERRIDE,PLL.QUANTITY*PLL.PRICE_OVERRIDE Amount, PLL.PRICE_DISCOUNT,POCR.DOCUMENT_SHIPMENT_NUMBER SHIPMENT_NUM,POH.CURRENCY_CODE, PLL.ATTRIBUTE1, PLL.ATTRIBUTE2, PLL.ATTRIBUTE3, PLL.ATTRIBUTE4, PLL.ATTRIBUTE5, PLL.ATTRIBUTE6, PLL.ATTRIBUTE7, PLL.ATTRIBUTE8, PLL.ATTRIBUTE9, PLL.ATTRIBUTE10, PLL.ATTRIBUTE11, PLL.ATTRIBUTE12, PLL.ATTRIBUTE13, PLL.ATTRIBUTE14, PLL.ATTRIBUTE15, PLL.ATTRIBUTE_CATEGORY, to_number(NULL) QUANTITY, to_number(NULL) QUANTITY_RECEIVED, PLL.QUANTITY_BILLED, PLL.QUANTITY_ACCEPTED, PLL.QUANTITY_REJECTED,PLL.QUANTITY_CANCELLED, PLL.QUANTITY_SHIPPED, PLL.CLOSED_CODE,PLL.CLOSED_FLAG,PLL.CANCEL_FLAG,PLL.APPROVED_FLAG, PLL.USSGL_TRANSACTION_CODE,PLL.ACCRUE_ON_RECEIPT_FLAG, PLL.FREIGHT_TERMS_LOOKUP_CODE,PLL.FOB_LOOKUP_CODE, PLL.ENCUMBERED_FLAG,PLL.UNENCUMBERED_QUANTITY,PLL.SHIP_VIA_LOOKUP_CODE, PLL.SHIP_TO_LOCATION_ID,POH.REVISION_NUM,PLL.SHIP_TO_ORGANIZATION_ID, POL.LINE_NUM,PLL.SHIPMENT_TYPE,POL.ALLOW_PRICE_OVERRIDE_FLAG, POL.NOT_TO_EXCEED_PRICE,PLL.FROM_HEADER_ID, NVL(HRL.LOCATION_CODE, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY),1,20)) LOCATION_CODE, POCR.NEW_QUANTITY,POCR.NEW_PROMISED_DATE,POCR.REQUEST_STATUS, NVL(POCR.NEW_PRICE,NVL(POCR.OLD_PRICE,PLL.PRICE_OVERRIDE)) NEW_PRICE,POCR.REQUEST_REASON, NVL(POCR.NEW_PRICE,NVL(POCR.OLD_PRICE,PLL.PRICE_OVERRIDE))*NVL(POCR.NEW_QUANTITY,PLL.QUANTITY) NewAmount, null ShipmentStatus, NULL SUPPLIER_ORDER_LINE_NUMBER,POCR.NEW_SUPPLIER_ORDER_LINE_NUMBER, PLL.NEED_BY_DATE,to_date(null),to_date(null), 'DATE_TYPE','QTY_TYPE','SUP_ORDER_TYPE','SHIP_TYPE','PRICE_BRK_TYPE', HRO.NAME ORGANIZATION_NAME,'REQUESTOR','REQUESTOR_ID','REQ_TYPE', 'AMOUNT_FORMATTED',NULL CONSIGNED_FLAG,'CONSIGNED',NULL VMI_FLAG,'VMI','ACTION','ACTION_TYPE','REASON_TYPE', NVL(PLL.DROP_SHIP_FLAG,'N') DROP_SHIP_FLAG,'SPLIT_TYPE', POCR.PARENT_LINE_LOCATION_ID,'SHIP_PRICE_DIFF_TYPE', 'ENTITY_TYPE',null LINE_TYPE_CODE, null GLOBAL_AGREEMENT_FLAG, null JOB_TITLE,null JOB_DESCRIPTION,'Y' IS_SPLIT, null LINE_PURCHASE_BASIS,to_number(null) SHIP_AMOUNT, to_number(null) AMOUNT_RECEIVED,to_number(null) AMOUNT_BILLED,to_number(null) AMOUNT_CANCELLED, to_number(null) AMOUNT_REJECTED,to_number(null) AMOUNT_ACCEPTED, to_date(null) SALES_ORDER_UPDATE_DATE,null SPLIT_SHIP, PLL.SOURCE_SHIPMENT_ID, POH.SEGMENT1 PO_NUM, POL.CONTRACTOR_FIRST_NAME || ' ' || POL.CONTRACTOR_LAST_NAME CONTRACTOR_NAME FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL, PO_CHANGE_REQUESTS POCR,HR_LOCATIONS_ALL_TL HRL, PO_HEADERS_ALL POH,HZ_LOCATIONS HZ, PO_LOOKUP_CODES POLC, PO_LINES_ALL POL, HR_ALL_ORGANIZATION_UNITS_TL HRO WHERE PLL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID (+) AND PLL.SHIP_TO_LOCATION_ID =HZ.LOCATION_ID (+) AND HRL.LANGUAGE (+) = USERENV('LANG') AND PLL.LATEST_EXTERNAL_FLAG= 'Y' AND HRO.ORGANIZATION_ID (+) = PLL.SHIP_TO_ORGANIZATION_ID AND HRO.LANGUAGE (+)= userenv('LANG') AND PLL.PO_LINE_ID = POL.PO_LINE_ID AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID AND POLC.LOOKUP_CODE = NVL(PLL.CLOSED_CODE, 'OPEN') AND POLC.LOOKUP_TYPE = 'DOCUMENT STATE' AND PLL.PO_HEADER_ID = POCR.DOCUMENT_HEADER_ID AND PLL.LINE_LOCATION_ID = POCR.PARENT_LINE_LOCATION_ID AND POCR.CHANGE_ACTIVE_FLAG = 'Y' AND POCR.REQUEST_LEVEL = 'SHIPMENT') QRSLT WHERE (PO_LINE_ID = :1) at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:888) at oracle.apps.fnd.framework.webui.OAPageErrorHandler.prepareException(OAPageErrorHandler.java:1145) at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(OAPageBean.java:2898) at oracle.apps.fnd.framework.webui.OAPageBean.renderDocument(OAPageBean.java:2700) at OA.jspService(OA.jsp:48) at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317) at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465) at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106) at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803) at java.lang.Thread.run(Thread.java:534) ## Detail 0 ## java.sql.SQLException: ORA-01006: bind variable does not exist


      I've even looked at the piece of code that is setting the where clause:

      if(flag)
      {
      RowSet rowset = (RowSet)super.getAttributeInternal(i);
      OADBTransactionImpl oadbtransactionimpl = (OADBTransactionImpl)getApplicationModule().getTransaction();
      if(oadbtransactionimpl != null)
      {
      String s = (String)oadbtransactionimpl.getTransientValue("PO_RELEASE_ID");
      String s1 = (String)oadbtransactionimpl.getTransientValue("REQ_HEADER_ID");
      String tmp = (String)oadbtransactionimpl.getTransientValue("REQLINE_ID");
      OAViewObject oaviewobject = (OAViewObject)rowset.getViewObject();
      String s2 = oaviewobject.getWhereClause();
      if(!rowset.isExecuted())
      {
      if(s1 != null && s1.length() > 0)
      {
      String s3 = " PO_LINE_ID in (select po_line_id from PO_DISTRIBUTIONS_ALL pod , po_req_distributions_all pord, po_requisition_lines_all por where pord.DISTRIBUTION_ID = pod.REQ_DISTRIBUTION_ID and pord.requisition_line_id = por.requisition_line_id and por.requisition_header_id= :1 ) ";
      if(s != null && s.length() > 0)
      s3 = s3 + " AND PO_RELEASE_ID = :2 AND PO_LINE_ID = :3 ";
      else
      s3 = s3 + " AND PO_RELEASE_ID is null AND PO_LINE_ID = :2 ";
      oaviewobject.setWhereClause(s3);
      rowset.setWhereClauseParams(null);
      rowset.setWhereClauseParam(0, s1);
      if(s != null && s.length() > 0)
      rowset.setWhereClauseParam(1, s);
      rowset.executeQuery();
      } else
      if(s != null && s.length() > 0)
      {
      oaviewobject.setWhereClause("PO_RELEASE_ID = :1 AND PO_LINE_ID = :2");
      rowset.setWhereClauseParams(null);
      rowset.setWhereClauseParam(0, s);
      rowset.executeQuery();
      } else
      if(s == null || s.equals(""))
      {
      oaviewobject.setWhereClause("PO_RELEASE_ID is null AND PO_LINE_ID = :1 ");
      rowset.executeQuery();
      }
      oaviewobject.setWhereClause(s2);
      }

      Am I not forwarding the page correctly? Oddly enough the other buttons work on the screen after clicking my link.

      Thanks, Graeme
        • 1. Re: ORA-01006: bind variable does not exist
          557833
          Put the debug statements and see in which scenario the bind variable setting code is failing.

          --Shiv                                                                                                                                                                                                               
          • 2. Re: ORA-01006: bind variable does not exist
            Kalimuthu V-Oracle
            Graeme ,

            I could see the select query have only :1, (only one bind parameter),

            but in the where clause you are setting 3 variables,
            check that is for the same VO or diff VO.

            And get the query and check you are setting all the bind values.

            With Regards,
            Kali.
            OSSI.
            • 3. Re: ORA-01006: bind variable does not exist
              TapashRay
              Kali, there are additional where clause in the code and there are other bind variables there.
              I would suggest to relook at the query and do setWhereClauseParams in correct place,so that more then requireed values for bind variables are not set.

              Thanks
              Tapash
              • 4. Re: ORA-01006: bind variable does not exist
                624291
                Thanks for the replies. I did figure it out, oddly enough when the rowset was bound it failed (only after clicking my link though) I would bind the variable and run the execute query and it would fail. However, the executeQuery method worked on the oaviewobject. So I basically had to override their method in my custom class to return the oaviewobject instead of the row. The odd part to me still, is that the where clause was the same for both, but one fails.

                - Graeme.
                • 5. Re: ORA-01006: bind variable does not exist
                  557833
                  Didn't notice it earlier that you are using rowset. It should be VO.

                  --Shiv