7

I have a method in Dao Class that returns List<Object[]> back and I am using named Query

public List<Object[]> getListByCustomer(Session session, int customerId, List<Integer> strIds) {
  Query namedQuery = session.createSQLQuery(QueryConstants.EXPORT);
  namedQuery.setParameter("customer", customerId);
  namedQuery.setParameter("stringId", strIds);
  List<Object[]> objects = namedQuery.list();
  return objects;
}

I want to pass List<Integer> strIds in stringId into the named query as follows :

public class QueryConstants {
  public static final String EXPORT = 
    "SELECT sv.NAME, sv.TYPE, sv.CLIENT_ADDRESS, sv.NAME_REDUNDANT, sv.DEPARTURE_DATE, s1.CODE,sv.STATE, sv.CODE "
    + "FROM VIEW sv, PROCESS p1, SET s1 " 
    + "WHERE sv.R_ID = p1.R_ID and p1.ISSUER_ID = s1.USER_ID and sv.CUSTOMER_ID = :customer and sv.R_ID IN (:stringId)";
}

But I get ORA-00932: inconsistent datatypes: expected NUMBER got BINARY.

Also when I remove sv.R_ID IN (:stringId) from the query it works fine and when I pass Integer(strIds) instead of List<Integer> strIds into the query it works fine.

I'm using Oracle 10g.

techGaurdian
  • 732
  • 1
  • 14
  • 35
  • Please refer http://stackoverflow.com/questions/4378824/adding-in-clause-list-to-a-jpa-query – Hirak Apr 22 '14 at 11:31
  • yes tried with removing (...) i.e sv.R_ID IN :stringId still same error. – techGaurdian Apr 22 '14 at 11:35
  • What is your JPA vendor? Also can you try namedQuery.setParameter("stringId", Arrays.toString(strIds.toArray())); with or without the (...) ? – Hirak Apr 22 '14 at 11:40
  • You can't bind a List for use in an `IN` clause. You'll need to convert it to an Oracle array object, treat that as a table, and use a join. – Alex Poole Apr 22 '14 at 11:41
  • Does anybody know if parameter binding for IN clauses is supported in SQL queries (as opposed to JPQL or HQL)? Most likely not. – Codo Apr 22 '14 at 11:42
  • @Codo - in plain SQL, no, each value in the `IN` clause has to be bound individually. [Or you can use an array](http://stackoverflow.com/a/21034016/266304); as a join, or I guess as a subquery within the `IN`. – Alex Poole Apr 22 '14 at 11:52

6 Answers6

30

This is a very misleading error, and may root from different causes, for me I was setting a parameter that it was supposedly a number but at runtime it was setting null, hence it was binary. On a separate occasion got this error due to bean creation error in spring and was not setting the parameter correctly as well.

Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
mel3kings
  • 8,857
  • 3
  • 60
  • 68
24

I think you just need to use 

 IN :stringId

instead of

 IN (:stringId)

For JPA 

namedQuery.setParameter("stringId", strIds);

is correct, but for Hibernate you should use

namedQuery.setParameterList("stringId", strIds);
Michael Legart
  • 800
  • 4
  • 7
4

I encountered this same exception and found the below reason for that -

In my entity, a field was mapped to a custom object (Parent child relationship - @ManyToOne). Later, the relationship annotation was removed by developer but the datatype was not changed.

After removing the @ManyToOne annotation, the @Column annotation should have been used with appropriate data type (Integer).

Ujjwal
  • 603
  • 12
  • 23
4

Case your param is list. If list is empty then raise error, you must check that list not empty to avoid error. Case your param is single value. Let use TO_NUMBER(:your_param) to avoid error. It working on me.

Phan Kieu Hung
  • 123
  • 1
  • 7
0

In my case, I was using HQL (in the repository of spring data) with an Entity mapped with @Enumerated (ORDINAL). I was trying to use the the enum object directly in the where clausule. The solution was to use TO_NUMBER(:your_param) as mentioned by the member above.

Fábio Lamartine
  • 1
0

I got the same error but for a different reason. In my case it was due to the order the parameters were supplied being different from the order defined in the query. I assumed (wrongly) that because the parameters were named the order didn't matter. Sadly, it seems like it does.

Rocinante
  • 1