sireesha tummala
asked on
java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
We are seeing java.sql.SQLSyntaxErrorExc eption: ORA-01722: invalid number in production when calling a stored proc and this stored procedure call is working fine in production from two months.
We have checked in DBA Team and as per admins the call never came to them.We checked all the transformations and parameter setting it looks good.(it's working now also in production trying to find the root cause for it).
It's failing in the below code snippet:
cs.execute();--from the stack trace
// get out parameters
int statusCode = cs.getInt(31);
String errorMessage = cs.getString(32);
if (statusCode < 0) {
logHelper.writeErrorLog(
LogErrorTypes.Processor_Up date_Error ,
errorMessage, getMessageXMLAsString(leve l1Message) ,
"CALL Complete", "", null);
}
Stored Proc: CALL XXXXXXXX.YYYYYYYY(?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?)";
If anyone can shed lights on this issue.it would be great ...Appreciate your help
We are using weblogic 12c
oracle.jdbc.OracleDriver
java.sql.SQLSyntaxErrorExc eption: ORA-01722: invalid number
at oracle.jdbc.driver.T4CTTIo er.process Error(T4CT TIoer.java :445)
at oracle.jdbc.driver.T4CTTIo er.process Error(T4CT TIoer.java :396)
at oracle.jdbc.driver.T4C8Oal l.processE rror(T4C8O all.java:8 79)
at oracle.jdbc.driver.T4CTTIf un.receive (T4CTTIfun .java:450)
at oracle.jdbc.driver.T4CTTIf un.doRPC(T 4CTTIfun.j ava:192)
at oracle.jdbc.driver.T4C8Oal l.doOALL(T 4C8Oall.ja va:531)
at oracle.jdbc.driver.T4CCall ableStatem ent.doOall 8(T4CCalla bleStateme nt.java:20 4)
at oracle.jdbc.driver.T4CCall ableStatem ent.execut eForRows(T 4CCallable Statement. java:1041)
at oracle.jdbc.driver.OracleS tatement.d oExecuteWi thTimeout( OracleStat ement.java :1329)
at oracle.jdbc.driver.OracleP reparedSta tement.exe cuteIntern al(OracleP reparedSta tement.jav a:3593)
at oracle.jdbc.driver.OracleP reparedSta tement.exe cute(Oracl ePreparedS tatement.j ava:3694)
at oracle.jdbc.driver.OracleC allableSta tement.exe cute(Oracl eCallableS tatement.j ava:4755)
We have checked in DBA Team and as per admins the call never came to them.We checked all the transformations and parameter setting it looks good.(it's working now also in production trying to find the root cause for it).
It's failing in the below code snippet:
cs.execute();--from the stack trace
// get out parameters
int statusCode = cs.getInt(31);
String errorMessage = cs.getString(32);
if (statusCode < 0) {
logHelper.writeErrorLog(
LogErrorTypes.Processor_Up
errorMessage, getMessageXMLAsString(leve
"CALL Complete", "", null);
}
Stored Proc: CALL XXXXXXXX.YYYYYYYY(?,?,?,?,
If anyone can shed lights on this issue.it would be great ...Appreciate your help
We are using weblogic 12c
oracle.jdbc.OracleDriver
java.sql.SQLSyntaxErrorExc
at oracle.jdbc.driver.T4CTTIo
at oracle.jdbc.driver.T4CTTIo
at oracle.jdbc.driver.T4C8Oal
at oracle.jdbc.driver.T4CTTIf
at oracle.jdbc.driver.T4CTTIf
at oracle.jdbc.driver.T4C8Oal
at oracle.jdbc.driver.T4CCall
at oracle.jdbc.driver.T4CCall
at oracle.jdbc.driver.OracleS
at oracle.jdbc.driver.OracleP
at oracle.jdbc.driver.OracleP
at oracle.jdbc.driver.OracleC
can you post the data and the procedure's parameter declarations?
maybe you're getting an implicit conversion that usually works, but as slightwv noted, for one particular set of data it failed.
maybe you're getting an implicit conversion that usually works, but as slightwv noted, for one particular set of data it failed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you guys for your inputs.we narrowed down the issue Stored proc changed in end system
If NOTHING in the system changed, then the data processed by the system did.
Make sure you aren't expecting a number and the data is now returning a non-numeric string.
I see an XML reference in the code snippet. I cannot remember the exact way I've seen an "invalid number" with XML but do remember seeing it once in a while. Maybe with a null node or null document but I cannot be sure. If you are returning/parsing XML, double check the XML being returned when the error happens.