Combine JPA Query annotation with Oracle sample method

March 8, 2022

i am trying to pass a parameter into a JPA query
Example code

 @Query(value =
              "select *\n"
            + "from adress sample(:percentile)\n"
            + "where adress.number in (:adressNumbers)\n"
            + "fetch first (:rows) rows only,"
            + "nativeQuery = true
           List<X> sampleExample(Integer rows, List<Integer> adressNumbers, Double percentile)

But i get an error because of the sample(:percentile).
If i just hardcode a number in there it works but not with a param.
Is there a way to escape the brackets or something similar?
Thx

>Solution :

The error is on the following part

from adress sample(:percentile)

Unfortunately it does not belong to the where part of the query and so the parameter passed :percentile can’t be bound to the query.

There is not any quick fix around this. You won’t be able to use a method parameter that will be able to be bound in the query in the part that you want it to be, because parameters can only be bound in the where part of the query.