We are retrieving data from oracle using spring data JDBC
We are using org.springframework.data.relational.core.dialect.OracleDialect to retrieve data from database.
It is working as expected when we have a repository that uses CrudRepository
But if we modify the repository which extends PagingAndSortingRepository to retrieve based on the page number, we are getting an exception.
Based on the analysis, we identified that queries generated by LIMIT_CLAUSE and LOCK_CLAUSE do not adhere to Oracle.
Is there an Oracle dialect to generate a proper limit query which is an instance of org.springframework.data.relational.core.dialect.Dialect?
The only available OracleDialect is based on the AnsiDialect` and Oracle12c is supposed to support the ANSI standard.
Further investigation leads to the suspicion that the ANSI standard allows multiple variants and AnsiDialect creates a clause that does not work with Oracle12 although it gets accepted by OracleXE18 which is used for testing.
Spring Data JDBC currently creates clauses of the form OFFSET %d ROWS FETCH FIRST %d ROWS ONLY. Which according to https://dba.stackexchange.com/questions/30452/ansi-iso-plans-for-limit-standardization is conforming to the standard.
But https://stackoverflow.com/a/24046664/66686 hints that Oracle12 might require OFFSET %d ROWS FETCH NEXT %d ROWS ONLY
As a workaround you may register a custom dialect as described in https://spring.io/blog/2020/05/20/migrating-to-spring-data-jdbc-2-0#dialects