Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3400
  • Last Modified:

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

We are seeing java.sql.SQLSyntaxErrorException: 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_Update_Error,
                                          errorMessage, getMessageXMLAsString(level1Message),
                                          "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.SQLSyntaxErrorException: ORA-01722: invalid number

                at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
                at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
                at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
                at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
                at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
                at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
                at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
                at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1041)
                at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
                at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3593)
                at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3694)
                at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4755)
0
sireesha tummala
Asked:
sireesha tummala
1 Solution
 
slightwv (䄆 Netminder) Commented:
Things that used to work and all of a sudden stopped working implies there is a change.

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.
0
 
sdstuberCommented:
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.
0
 
Amitkumar PanchalSr. ConsultantCommented:
Things that used to work and all of a sudden stopped working implies there is a change.

I agree with both the experts. Something is changed in your code base or data. So you should check the following.
1. Is your java code changed (ie. addition/removal of parameters to the proc accidentally) ?
2. Is your stored proc is changed ?
3. Can you check/provide specific set of params which causes this error ? If answer to first two is NO then definitely it is your data which causes an issue.
0
 
sireesha tummalaAuthor Commented:
Thank you guys for your inputs.we narrowed down the issue Stored proc changed in end system
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now