Databases
StackOverflow2013
Postsdbo
POinvalid column type ~ Sending ArrayList<String> to a pl/sql createdNameQuery
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POinvalid column type ~ Sending ArrayList<String> to a pl/sql createdNameQuery
    text
    copied!<p>This is related to a past <a href="https://stackoverflow.com/questions/6879270/how-to-send-send-an-array-of-objects-to-a-namedquery-that-needs-the-id-of-all-t">question</a> of mine.</p> <p>I am receiving a <code>List&lt;Employee&gt;</code> and grabbing the ids from the <code>Employee</code> objects and putting them in an <code>ArrayList&lt;String&gt;</code> then sending that <code>ArrayList</code> as a parameter in the <code>createdNameQuery</code>. 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.</p> <p>function in my repository:</p> <pre><code>public List&lt;RequestByRequester&gt; getRequestsByRequesters( List&lt;Employee&gt; employeeList) throws NoDataFoundException { List&lt;String&gt; idList = new ArrayList&lt;String&gt;(); for(Employee emp : employeeList) { idList.add(emp.getId().toString()); } log.debug("Input params[requesters=" + idList + "]"); List&lt;RequestByRequester&gt; resultList = getEm().createNamedQuery( "requestByRequestor.getRequestsByRequesters", RequestByRequester.class) .setParameter(1,idList) .getResultList(); if(resultList == null || resultList.size() &lt;= 0) throw new NoDataFoundException("No requests found by requesters."); else return resultList; } </code></pre> <p>My named query <strong>requestByRequestor.getRequestsByRequesters</strong> below:</p> <pre><code> @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" ) </code></pre> <p>EDIT: Adding exceptions as requested.</p> <p>This exception when I use <code>:ids</code> in the query:</p> <p><code>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")</code></p> <p>Exception when I use <code>?1</code> or <code>(?)</code> in the query :</p> <p><code>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 =&gt; [[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")</code></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload