Native query IN clause throwing sql error

Question

Below query gives me error like Missing IN or OUT parameter at index:: 1 I tried it without brackets in the IN parameters like ... WHERE p.SALES_TYPE IN :types. but still the same error.

public List getWithinXDays(ArrayList<String> salesTypes,String sId, String  xdays) {

    Query q  = em.createNativeQuery("SELECT p.ORDER_ID FROM formtable p WHERE p.SALES_TYPE IN (:types) AND p.MCODE=:sid AND TRUNC(SYSDATE - p.creationdate) <=:days");
    q.setParameter("types",salesTypes);
    q.setParameter("sid",sId);
    q.setParameter("days",xdays);
    List salesOrderRow = q.getResultList();
    return salesOrderRow;
}

stacktrace :

<Jan 10, 2017 4:12:07 PM SGT> <Warning> <EclipseLink> <BEA-2005000> <2017-01-10 16:12:07.551--UnitOfWork(29679273)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
Error Code: 17041

index 1 should be 'sid' param if im not mistaken. could you check/debug if it's null? — geneqew, Jan 10 '17 at 08:28
Possible duplicate of [JPA passing list to IN clause in named native query](http://stackoverflow.com/questions/6277807/jpa-passing-list-to-in-clause-in-named-native-query) — Neil Stockton, Jan 10 '17 at 08:43

score 1 · Accepted Answer · edited May 23 '17 at 12:24

1

JPA doesn't support array parameters for native queries. Either rewrite your code to use JPQL, or set each entry for IN clause like it's described in the following answer: JPA passing list to IN clause in named native query

edited May 23 '17 at 12:24

Community

1
1

answered Jan 10 '17 at 08:31

bsiamionau

8,099
4
46
73

Native query IN clause throwing sql error

1 Answers1

Linked