This discussion is archived
3 Replies Latest reply: Aug 17, 2012 10:58 AM by cdelahun RSS

Intermittent ORA-01722 error while trying to query thru Eclipselink

newToplinkUser Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points