Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-10-02
4
Medium Priority
?
3,240 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
[X]
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
4 Comments
 
LVL 77

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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

705 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