Issue
I have written following spring data JPA native query.
@Query(value = "SELECT ID, REFERENCE FROM ORDER_MATCH where ORDER_REF IN :orderRef AND STATUS = :status", nativeQuery = true)
List<Object[]> retriveData(List<String> orderRef, String status);
this code gives me the following error.
SQL Error : 936, SQLState : 42000
ORA-00936 : missing expression
org.springframework.dao.InvalidDataAccessResourceUsageException : could not extract ResultSet; SQL[n/a]; nested exception is org.hibernate.exception.SQLGrammerException: could not extract ResultSet
However, If I removed one parameter, then code works, for example following two combinations works without any issue, problem happens when I have two parameters, could anybody help me to identify what is going wrong here. Thank you so much in advance.
working combinations with one parameter
@Query(value = "SELECT ID, REFERENCE FROM ORDER_MATCH where ORDER_REF IN :orderRef" , nativeQuery = true)
List<Object[]> retriveData(List<String> orderRef);
@Query(value = "SELECT ID, REFERENCE FROM ORDER_MATCH where STATUS = :status", nativeQuery = true)
List<Object[]> retriveData(String status);
Just to add some more details, I have used :? way of passing parameters and it also results in same error
@Query(value = "SELECT ID, REFERENCE FROM ORDER_MATCH where ORDER_REF IN (?1) AND STATUS = ?2", nativeQuery = true)
List<Object[]> retriveData(List<String> orderRef, String status);
Solution
Try something like this:
@Query(value = "SELECT ID, REFERENCE FROM ORDER_MATCH where ORDER_REF IN :orderRef AND STATUS = :status", nativeQuery = true)
List<Object[]> retriveData(@Param("orderRef") List<String> orderRef, @Param("status") String status);
Also please note that as it's stated in the hibernate documentation:
The list of values can come from a number of different sources. In the
constructor_expression
andcollection_valued_input_parameter
, the list of values must not be empty; it must contain at least one value.
Answered By - SternK