For the below query, I am getting an error, ORA-01867: the interval is invalid

  @Query(value="select * from Customer s where s.CUS_ID=:customerid and s.INSERT_TM < systimestamp - INTERVAL ':threshold' MINUTE", nativeQuery=true)
    @List<Customers> getCustomers(@Param("customerid") String customerid, @Param("threshold") Integer threshold )

Error is with this statement

INTERVAL ':threshold' MINUTE

But if I hardcode the threshold field, then I am getting success response,

INTERVAL '5' MINUTE

I don't want to hardcode. Can someone tell me how do I fix this?

CodePudding user response：

You might want to do below trick like below to make it work:

@Query"select * from Customer s where s.CUS_ID=:customerid and s.INSERT_TM < systimestamp - :threshold*INTERVAL '1' MINUTE"
 @List<Customers> getCustomers(@Param("customerid") String customerid, @Param("threshold") String threshold )

Just to prove multiplication works with interval, you can check this fiddle