Win a copy of Five Lines of Code this week in the OO, Patterns, UML and Refactoring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

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

 
Ranch Hand
Posts: 45
  • 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
 
Ranch Hand
Posts: 75
  • 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: 45
  • 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.
 
Sheriff
Posts: 21972
106
Eclipse IDE Spring VI Editor Chrome Java Ubuntu Windows
  • 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.
 
No matter. Try again. Fail again. Fail better. This time, do it with this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic