?
Solved

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

Posted on 2014-10-02
4
Medium Priority
?
3,776 Views
Last Modified: 2014-10-07
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
Comment
Question by:sireesha tummala
4 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40359275
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40359348
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
 
LVL 21

Accepted Solution

by:
Amitkumar P earned 1500 total points
ID: 40360218
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
 

Author Comment

by:sireesha tummala
ID: 40367680
Thank you guys for your inputs.we narrowed down the issue Stored proc changed in end system
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The purpose of this article is to demonstrate how we can use conditional statements using Python.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question