Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-10-02
4
2,651 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 76

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 Panchal earned 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MULTIPLE DATE QUERY 15 91
Oracle Listener Not Starting 11 52
Problem with duplicate records in Oracle query 16 40
Linux Real Time Memory on Distinct 24 Cycle 7 29
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
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 teaches viewers about errors in exception handling.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

860 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