invalid column type ~ Sending ArrayList<String> to a pl/sql createdNameQuery

Question

This is related to a past question of mine.

I am receiving a List<Employee> and grabbing the ids from the Employee objects and putting them in an ArrayList<String> then sending that ArrayList as a parameter in the createdNameQuery. I am receiving an invalid column type sql exception. I tested the query in pl/sql developer and it returned fields. I have tried building out a string of ids by placing a coma between each id and sending that but, I received an exception from that attempt. I am curious if I have the query setup incorrectly or sending the data incorrectly.

function in my repository:

public List<RequestByRequester> getRequestsByRequesters(
        List<Employee> employeeList) 
        throws NoDataFoundException {

    List<String> idList = new ArrayList<String>();
    for(Employee emp : employeeList) {          
        idList.add(emp.getId().toString());
    }

    log.debug("Input params[requesters=" + idList + "]");

    List<RequestByRequester> resultList = getEm().createNamedQuery(
            "requestByRequestor.getRequestsByRequesters", RequestByRequester.class)
            .setParameter(1,idList)
            .getResultList();

        if(resultList == null || resultList.size() <= 0)
            throw new NoDataFoundException("No requests found by requesters.");
        else
            return resultList;
}

My named query requestByRequestor.getRequestsByRequesters below:

   @NamedNativeQuery(
    name = "requestByRequestor.getRequestsByRequesters",
    resultClass = RequestByRequester.class,
    query = "SELECT EMP.EMPL_FIRST_NAME || ' ' || EMP.EMPL_LAST_NAME REQUESTER," +
            "       R.RQST_ID RQST_ID," +
            "       R.TITLE TITLE," +
            "       R.DESCRIPTION DESCRIPTION," +
            "       DECODE(R.RESOLUTION_DATE, NULL, 'Open', 'Closed') STATUS" +
            "  FROM TARTS.REQUESTS R, SYS_EMPLOYEES EMP" +
            " WHERE R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID" +
            "   AND EMP.EMPL_ID IN (?)" +
            " ORDER BY 1, 5 DESC, 2"
    )

EDIT: Adding exceptions as requested.

This exception when I use :ids in the query:

Internal Exception: java.sql.SQLException: Missing IN or OUT parameter at index:: 1 Error Code: 17041 Call: SELECT EMP.EMPL_FIRST_NAME || ' ' || EMP.EMPL_LAST_NAME REQUESTER, R.RQST_ID RQST_ID, R.TITLE TITLE, R.DESCRIPTION DESCRIPTION, DECODE(R.RESOLUTION_DATE, NULL, 'Open', 'Closed') STATUS FROM TARTS.REQUESTS R, SYS_EMPLOYEES EMP WHERE R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID AND EMP.EMPL_ID IN :ids ORDER BY 1, 5 DESC, 2 Query: ReadAllQuery(name="requestByRequestor.getRequestsByRequesters" referenceClass=RequestByRequester sql="SELECT EMP.EMPL_FIRST_NAME || ' ' || EMP.EMPL_LAST_NAME REQUESTER, R.RQST_ID RQST_ID, R.TITLE TITLE, R.DESCRIPTION DESCRIPTION, DECODE(R.RESOLUTION_DATE, NULL, 'Open', 'Closed') STATUS FROM TARTS.REQUESTS R, SYS_EMPLOYEES EMP WHERE R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID AND EMP.EMPL_ID IN :ids ORDER BY 1, 5 DESC, 2")

Exception when I use ?1 or (?) in the query :

Internal Exception: java.sql.SQLException: Invalid column type Error Code: 17004 Call: SELECT EMP.EMPL_FIRST_NAME || ' ' || EMP.EMPL_LAST_NAME REQUESTER, R.RQST_ID RQST_ID, R.TITLE TITLE, R.DESCRIPTION DESCRIPTION, DECODE(R.RESOLUTION_DATE, NULL, 'Open', 'Closed') STATUS FROM TARTS.REQUESTS R, SYS_EMPLOYEES EMP WHERE R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID AND EMP.EMPL_ID IN ? ORDER BY 1, 5 DESC, 2 bind => [[2192, 632]] Query: ReadAllQuery(name="requestByRequestor.getRequestsByRequesters" referenceClass=RequestByRequester sql="SELECT EMP.EMPL_FIRST_NAME || ' ' || EMP.EMPL_LAST_NAME REQUESTER, R.RQST_ID RQST_ID, R.TITLE TITLE, R.DESCRIPTION DESCRIPTION, DECODE(R.RESOLUTION_DATE, NULL, 'Open', 'Closed') STATUS FROM TARTS.REQUESTS R, SYS_EMPLOYEES EMP WHERE R.EMPL_ID_REQUESTED_BY = EMP.EMPL_ID AND EMP.EMPL_ID IN ? ORDER BY 1, 5 DESC, 2")

Answer 1

The issue (I think, because you did not include the definition of your named query), is that you are executing a native SQL query, and a List is not a valid SQL/JDBC parameter value.

EclipseLink support List parameters for JPQL queries, but not for native SQL queries.

You need to either use JPQL, or define each of the parameters in your SQL

i.e.

EMP.EMPL_ID IN (:id1, :id2, :id3)

.setParameter("id1", idList.get(0));
.setParameter("id2", idList.get(1));