• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Sgc Manorite
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34860
369
Eclipse IDE Java VI Editor
  • 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!
 
Jan Cumps
Bartender
Posts: 2599
12
C++ Linux Netbeans IDE
  • 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: 2599
12
C++ Linux Netbeans IDE
  • 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: 2599
12
C++ Linux Netbeans IDE
  • 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.
 
rajesh kethepalle
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 ]
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • 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.
 
James Basller
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!!!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic