3 Replies Latest reply: Aug 17, 2012 12:58 PM by cdelahun RSS

    Intermittent ORA-01722 error while trying to query thru Eclipselink

    newToplinkUser
      We are seeing this error from time to time, the same query works fine when run from SQLPLUS connecting to Oracle, any help would be appreciated.

      org.eclipse.persistence.exceptions.DatabaseException:
      Internal Exception: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

      Error Code: 1722
      Call: SELECT id, type, CSDKExtSys, CoveredTradeEXTSYS, CptyReqIdEXTSYS,
      CptyTradeIdEXTSYS, DirectFXTraderEXTSYS, FIX43EXTSYS, ISEXTSYS,
      JMSPROXYWAITTIME, OAORDERMATCHEDBYADAPTOR , OAORDERPUBLISHEDBYADAPTOR ,
      OAORDERRECEIVEDB
      YADAPTOR , ProviderEXTSYS, RFSQUOTESTATECHANGESNAPSHOT , RequestIdEXTSYS,
      TraderESPEXTSYS, UPI, AcptRecvdByAdpt, AcptRecvdByApp, AcptSentByAdpt,
      AcptSentByApp, AcptSentByUser, actualSettlementDate, bkOfficeId, businessExecu
      tionDate , channel, CLIENTCPUUSAGE , CLIENTMEMORYUSAGE , CLIENTPOLLLATENCY ,
      CLIENTTAG, CnfrmByUser, confirmDate, CPTYALEI, CPTYBLEI, coverTradeTxIds,
      coveredTradeTxId, createdDate, description, displayKey, entryDateTime, ET
      LTIMESTAMP, execDate, execFlags, ftOfficeId, guid, ISEVENTSMATCHPARAMETRIC,
      ISEVENTSMODIFIED, ISTEMPLATE, lastEventDate, MAKERMKTSNAPSHOT,
      MAKERORDERDLTCCY, MAKERORDERIDREF, MAKERORDERCHNL, makerRefId, MANUALLYUPDATED,
      MASKE
      DLP, maturityDate, modificationDateTime, modifiedDate, NOOFATTEMPTSBYCLIENT,
      note, NxtRateRecvdByApp, OrdMtchdByApp, OrdRecvdByApp, ORDERID,
      ORIGINATINGAMOUNT, origCptyId, ORIGINATINGORDERID, origUsrId, ownerCptyRef,
      PRICERE
      GENERATIONKEY, PRICEREGENERATIONSTATE, QuoteCreatedByApp, RateAcptByUser,
      RateAggrtByApp, RateDsplyByUser, RateEffective, RateQueriedByUser,
      RateRecvdByAdpt, RateRecvdByApp, RateRecvdByPrxy, RateRecvdByUser,
      RateSentByAdpt,
      RateSentByApp, RateSentByPrxy, RESPSENTBYADPT, RISKPOSITION, RjctRecvdByApp,
      RjctRecvdFrmPrvdr, RjctSentByApp, settlementDate, status, statusLastModified,
      STREAM, symbol, takerRefId, tradeDate, tid, TRIGGERREACHEDAT, unadjus
      tedMaturityDate, unadjustedSettlementDate, USERLATENCY, version, VIRTUALSERVER,
      VrfyRecvdByApp, VrfyRecvdFrmPrvdr, VrfySentByApp, settlementbclag,
      settlementbclagtype, settlementbchol1, settlementbchol2, settlementbchol3, se
      ttlementbcrollcnv, settlementDateTermLength, settlementDateTermUnit,
      termLength, termUnit, allocTrdId, bookAId, bookBId, brokerId, bsnsCtrId,
      calcAgentId, cntnTrdId, cptyAId, cptyBId, cptyCId, cptyDId,
      coveredTradeCounterpar
      ty, coveredTradeUser, cptyAllocTblId, entryUserId, exchngId, ovrFinEvtBldId,
      lastModifiedById, mkrCptyId, makerRequestId, MKRSDCPTYID, MKRSDUSRID, mkrUsrId,
      namespaceId, netTradeId, orgId, pfId, quoteId, requestId, SALESDLRC
      PTYID, SALESDLRCPTYUSERID, busSettDateRl, stateid, tkrCptyId, clsfId,
      tradeStatId, ownerId, TRIGGEREDBY, workflowStateMapId FROM IDCTRD WHERE ((id =
      ?) AND (type = ?))
      bind => [37243044, IdcFXSLegC]
      Query: ReadObjectQuery(referenceClass=FXSingleLegC )
      at
      org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
      at
      org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
        • 1. Re: Intermittent ORA-01722 error while trying to query thru Eclipselink
          cdelahun
          Searching for ORA-01722 shows that it occurs when an attempt to convert a string to a number fails. The query seems to use binding for the id and type fields, can you verify the type of the column in the database, and that the values passed in: [37243044, IdcFXSLegC] are correct? I would expect that the "37243044" passed into the query to be a java.lang.Number implementation, could you be passing in a string instead?

          Do any queries on the FXSingleLegC or other entities work?

          Best Regards,
          Chris
          • 2. Re: Intermittent ORA-01722 error while trying to query thru Eclipselink
            newToplinkUser
            The query is getting fired when we refresh the object using toplink session, my question is when we refresh an object, Eclipselink always uses the ObejctID (Primary key) to refresh the
            object, however in this case the type parameter was also sent, Chris, could you please explain?


            Thanks a lot,

            Kasi
            • 3. Re: Intermittent ORA-01722 error while trying to query thru Eclipselink
              cdelahun
              A refresh query is just a normal read object query that uses the primary key, but with a flag that is set so that it is forced to go the database and rebuild the object. If inheritance is involved, a descriminator column is used, so that subclasses can be filtered out and the correct java types built. I assume 'type' has been specified in the descriptors as the descriminator field, and that this class is a root or subclass in an inheritance heirarchy. If the type field is causing the problem, you will need to check that it is mapped correctly in the descriptors.

              Best Regards,
              Chris