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

Answer 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