File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
karthik swamy
Ranch Hand
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 75
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 42
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20381
46
Chrome Eclipse IDE Java Windows
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic