This week's book giveaway is in the Java in General forum.
We're giving away four copies of Event Streams in Action and have Alexander Dean & Valentin Crettaz on-line!
See this thread for details.
Win a copy of Event Streams in Action this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

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

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a function as follows:

public int countUser(String userId) throws SQLException {

int count = 0;
String query = "SELECT COUNT (user_id)" +
" FROM users a, resources b"
" WHERE a.resource_id = b.resource_id"
" AND b.resource_name = 'Silver'"
" AND b.is_client = 1"
" AND a.user_id = ?";

PreparedStatement stmt = con.prepareStatement(query);
stmt.setObject(1, new Integer(userId), java.sql.Types.INTEGER);
ResultSet rset = stmt.executeQuery();
while(rset.next()) {
count = rset.getInt(1);
}
return count;
}

I get java.sql.SQLException: ORA-01722: invalid number while executing the query. Can't figure where I am going wrong. This works fine many times and this exception is not consistent. Please help.
 
author & internet detective
Posts: 39392
763
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to JavaRanch!

Is it possible userId is not a number on some executions?
 
Sgc Manorite
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by Jeanne Boyarsky:
Welcome to JavaRanch!

Is it possible userId is not a number on some executions?



The userId never comes as a non-number or else we should be getting NumberFormatException when doing new Integer(userId). But even this exception is not coming up. Can't figure out where can the issue be? Please advise. Thanks!
 
Bartender
Posts: 2653
18
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can you please execute these two commands in sql*plus, and show us the results:
This might help us.
I would like to see the definition of:
users.resource_id
users.user_id
resources.resource_id
resources.resource_name
resources.is_client
 
Sgc Manorite
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by Jan Cumps:
can you please execute these two commands in sql*plus, and show us the results:
This might help us.
I would like to see the definition of:
users.resource_id
users.user_id
resources.resource_id
resources.resource_name
resources.is_client



Hi,
Here are the table descriptions...

TABLE users
Name Null? Type
----------------------------------------- -------- -----------------------
USER_ID NOT NULL NUMBER
RESOURCE_ID NOT NULL NUMBER

TABLE resources
Name Null? Type
----------------------------------------- -------- ------------------------
RESOURCE_ID NOT NULL NUMBER
RESOURCE_NAME VARCHAR2(100)
DESCRIPTION VARCHAR2(100)
IS_CLIENT_ACL NOT NULL NUMBER
 
Jan Cumps
Bartender
Posts: 2653
18
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Your java code says: " AND b.is_client = 1".
The table definition says: IS_CLIENT_ACL NOT NULL NUMBER.
Can you provide the definition of the field is_client?
When I look at your code, and verify the table definition, that field seems to be the only place where things could go wrong.

Regards, Jan
 
Sgc Manorite
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by Jan Cumps:
Hi,

Your java code says: " AND b.is_client = 1".
The table definition says: IS_CLIENT_ACL NOT NULL NUMBER.
Can you provide the definition of the field is_client?
When I look at your code, and verify the table definition, that field seems to be the only place where things could go wrong.

Regards, Jan



Sorry for the typo. The code is: AND b.is_client_acl = 1
 
Jan Cumps
Bartender
Posts: 2653
18
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As a last thing, can you please try to replace

with
Regards, Jan
 
Sgc Manorite
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by Jan Cumps:
As a last thing, can you please try to replace

with
Regards, Jan



Thanks for the help. I am not able to reproduce this issue on my local environment. Will have to monitor for this exception on production server after which I will update this post with my comments.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hai danish,
this is rajesh . i have given staffcode as number datatype in database and i have given setters and getters for staffcode as String is there any problem
 
Sgc Manorite
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jan,

I don't seem to be getting the exception any more from production server after doing the code change suggested by you. Appreciate it.
[ November 05, 2008: Message edited by: SGC Manorite ]
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Manorite,
I wish to point out that since the data-type for column USER_ID in table USERS is NUMBER, it can contain values that are larger than what java class "Integer" is able to handle.
By default, Oracle's JDBC driver maps the database NUMBER data-type to java class "java.math.BigDecimal".
Note that this is merely a FYI*.

Good Luck,
Avi.
 
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Sgc Manorite wrote:

Originally posted by Jan Cumps:
As a last thing, can you please try to replace

with
Regards, Jan



Thanks for the help. I am not able to reproduce this issue on my local environment. Will have to monitor for this exception on production server after which I will update this post with my comments.




Hi,

I also facing the same problem and also I am not able to regenerate this scenario. So, can you please tell me after doing change from setObject() to setInt() have you faced this problem anymore till date?

Thanks!!!
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!