[FIXED] Bind @param in a Jpa repository native query inside single quotations

Issue

I am looking for a way to bind a given param in a native query where the value has to be inside single quotations, like so:

@Transactional(readOnly = true)
@Query(value = " SELECT c.ID " +
               " FROM table1 clh " +
               " LEFT JOIN table2 nks " +
               " on clh.SERIAL = nks.SERIAL_REF " +
               " WHERE clh.CREATED_DATE >= :now - interval ':timeThreshold' HOUR " +
               " AND nks.SERIAL_REF IS NULL" , nativeQuery = true)
List<Long> getIdsWithoutAnswer (@Param("timeThreshold") Integer timeThreshold, @Param("now") LocalDateTime now);

However, when I try to run this, it results in hibernate not being able to bind the timeThreshold value as it is provided inside the single quotations ''.

Does anyone know how this can be resolved?

Solution

The problem you are having with your native Oracle query has to do with trying to bind a value to an interval literal. You can't do that. Instead, use the NUMTODSINTERVAL() function:

@Transactional(readOnly = true)
@Query(value = " SELECT c.ID " +
               " FROM table1 clh " +
               " LEFT JOIN table2 nks " +
               " on clh.SERIAL = nks.SERIAL_REF " +
               " WHERE clh.CREATED_DATE >= :now - numtodsinterval(:timeThreshold, 'hour') " +
               " AND nks.SERIAL_REF IS NULL" , nativeQuery = true)
List<Long> getIdsWithoutAnswer (@Param("timeThreshold") Integer timeThreshold, @Param("now") LocalDateTime now);

Answered By - Tim Biegeleisen
Answer Checked By - Marilyn (JavaFixing Volunteer)