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

[karthik swamy]

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]

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
SELECT ...... FROM table where something in ('1,2,3');
Make sure your query gets constructed as
SELECT ...... FROM table where something in (1,2,3);
Where 1, 2 and 3 are numerical values (assuming org_id is of type number in the DB).

[karthik swamy]

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
SELECT ...... FROM table where something in ('1,2,3');
Make sure your query gets constructed as
SELECT ...... FROM table where something in (1,2,3);
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]

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:

String strOrgId=(String)session.getAttribute("strOrgId");
String[] ids = strOrgId.split("'?,'?"); // splitting on commas, surrounded or not by single quotes
StringBuilder strQry1 = "SELECT (TO_CHAR(SYSDATE,'yddd')||MAX(SUBSTR(DE095,5)))+1 FROM APP_MAST_CBOUT WHERE org_id in (?;
for (int i = 1; i < ids.length; i++) // start at 1, there already is one ?
{
    strQry1.append(",?");
}
strQry1.append(')');
// strQry1 now contains SELECT (TO_CHAR(SYSDATE,'yddd')||MAX(SUBSTR(DE095,5)))+1 FROM APP_MAST_CBOUT WHERE org_id in (?,?,?)
PreparedStatement pst1 = dbcon.getConnection().prepareStatement(strQry1.toString());
for (int i = 0; i < ids.length; i++)
{
    pst1.setString(i + 1, ids[i]);
}
obj.runQuery(dbcon.getConnection(), pst1);

I'll move this thread to our JDBC forum.