my dog learned polymorphism*
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
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

karthik swamy
Ranch Hand

Joined: Mar 14, 2011
Posts: 42
hello everyone,
Although i think my code is correct but then also it si showing error,So please tell me where i am wrong.

here is my code
i am getting strOrgId as 1',2',3'

String strOrgId=(String)session.getAttribute("strOrgId");

String strQry1="SELECT (TO_CHAR(SYSDATE,'yddd')||MAX(SUBSTR(DE095,5)))+1 FROM APP_MAST_CBOUT WHERE org_id in (?)";
PreparedStatement pst1=null;
pst1=dbcon.getConnection().prepareStatement(strQry1);
pst1.setString(1,strOrgId);
obj.runQuery(dbcon.getConnection(),pst1);

then also showing me this error

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


Regards
Karthik Swamy
Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
Your code is not right. You are setting a string literal to the query in list where as DB expects the in list to be numbers.

The query which gets exected in your case will look like

Make sure your query gets constructed as

Where 1, 2 and 3 are numerical values (assuming org_id is of type number in the DB).
karthik swamy
Ranch Hand

Joined: Mar 14, 2011
Posts: 42
Sudheer Bhat wrote:Your code is not right. You are setting a string literal to the query in list where as DB expects the in list to be numbers.

The query which gets exected in your case will look like

Make sure your query gets constructed as

Where 1, 2 and 3 are numerical values (assuming org_id is of type number in the DB).


But Sir i am getting that id as 1','2','3 and after that i am just appending ' at front and end and passing as String to DB then why it is not working.
i mean SELECT ...... FROM table where something in ('1','2','3');
like this i need to pass.
so please suggest any solution.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19693
    
  20

The ? can only accept one single value. In your code the Oracle database is using an IN clause with only one value: 1',2',3'.

To use PreparedStatement with an IN clause you need to dynamically create and fill your preparedStatement:

I'll move this thread to our JDBC forum.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: java.sql.SQLException: ORA-01722: invalid number