ORA-01722 invalid number for where condition with IN

Asked Apr 21 '17 at 04:17

Active Apr 21 '17 at 06:55

Viewed 660 times

Here is my code:

String sql = "UPDATE EmployeeEXT SET bonus = 1 WHERE empId IN (:empIds); Query query = session.createSQLQuery(sql); query.setParameterList("empIds", employeeIds); query.executeUpdate();

EmployeeEXT Table structure is like

 EmployeeEXT_ID Number(19)
 BONUS          Number(5)
 EMPID          Number(19)

EmployeeEXT.java Pojo

Class EmployeeEXT {
 private long   employeeEXTId;
 private long   bonus;
 private Employee emp; //Many-TO-ONE Relation

  // setter and getter methods.
}

Hibernate parameterList setting long list for empIds parameter. But oracle giving ORA-01722 Invalid number.

I guess the reason is hibernate replacing 'empIds` parameter with a cama separated long values, so oracle feeling it as string, but when I sending single value it's working.

The same peace of code working fine for MYSQL.

Somebody help me to work fine with hibernate query parameterList.

edited Apr 21 '17 at 06:55

asked Apr 21 '17 at 04:17

Siva kumar

Removed tag [Java]. Not all MySQL code is Oracle compatible. You can use `to_number` as described [here](http://www.dba-oracle.com/sf_ora_01722_invalid_number.htm). – Rajeev Sreedharan Apr 21 '17 at 05:16
`to_number` only work for single value but in my case it may contain multiple values – Siva kumar Apr 21 '17 at 05:45
What's the datatype of this variable `employeeIds`? – Rajeev Sreedharan Apr 21 '17 at 05:49
`employeeIds` type is `ArrayList` – Siva kumar Apr 21 '17 at 05:59
You can try converting to `ArrayList` like this `employeeIds.stream().map(Integer::valueOf).collect(Collectors.toList());` This is also to check the your `Long` values are indeed in `Integer` range. – Rajeev Sreedharan Apr 21 '17 at 06:11
I think this is the answer to your question [link](http://stackoverflow.com/questions/17842211/how-to-use-an-arraylist-as-a-prepared-statement-parameter). – Seyran Apr 21 '17 at 07:29

ORA-01722 invalid number for where condition with IN

0 Answers0