Solved

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

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

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle sql query 7 62
Use of Exception to end a Loop 3 33
Oracle encryption 12 34
Oracle Query - Convert letters to numbers and display the difference 3 27
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
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.

822 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