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

[Sgc Manorite]

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]

Welcome to JavaRanch!

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

[Sgc Manorite]

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]

can you please execute these two commands in sql*plus, and show us the results:

desc users
desc resources

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]

Originally posted by Jan Cumps:
can you please execute these two commands in sql*plus, and show us the results:

desc users
desc resources

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]

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]

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]

As a last thing, can you please try to replace

stmt.setObject(1, new Integer(userId), java.sql.Types.INTEGER);

with

stmt.setInt(1, Integer.parseInt(userId));

Regards, Jan

[Sgc Manorite]

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

stmt.setObject(1, new Integer(userId), java.sql.Types.INTEGER);

with

stmt.setInt(1, Integer.parseInt(userId));

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]

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]

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]

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]

Sgc Manorite wrote:

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

stmt.setObject(1, new Integer(userId), java.sql.Types.INTEGER);

with

stmt.setInt(1, Integer.parseInt(userId));

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