aspose file tools*
The moose likes JDBC and the fly likes java.sql.SQLException: ORA-01722: invalid number Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java.sql.SQLException: ORA-01722: invalid number " Watch "java.sql.SQLException: ORA-01722: invalid number " New topic
Author

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

Sgc Manorite
Greenhorn

Joined: Jun 05, 2008
Posts: 12
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

Joined: May 26, 2003
Posts: 30764
    
156

Welcome to JavaRanch!

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sgc Manorite
Greenhorn

Joined: Jun 05, 2008
Posts: 12
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

Joined: Dec 20, 2006
Posts: 2503
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
Sgc Manorite
Greenhorn

Joined: Jun 05, 2008
Posts: 12
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

Joined: Dec 20, 2006
Posts: 2503
    
    8

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

Joined: Jun 05, 2008
Posts: 12
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

Joined: Dec 20, 2006
Posts: 2503
    
    8

As a last thing, can you please try to replace

with
Regards, Jan
Sgc Manorite
Greenhorn

Joined: Jun 05, 2008
Posts: 12
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

Joined: Jul 21, 2008
Posts: 3
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

Joined: Jun 05, 2008
Posts: 12
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

Joined: Oct 11, 2000
Posts: 1134

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

Joined: Sep 07, 2008
Posts: 58
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!!!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: java.sql.SQLException: ORA-01722: invalid number