1

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?

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
user1862354
  • 117
  • 11

1 Answers1

1

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

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • Thanks for your information. May I know how to do the same in Oracle 11g? – user1862354 Apr 13 '21 at 05:13
  • The syntax for Oracle11 requires an inner select. This is currently not supported by Spring Data JDBC – Jens Schauder Apr 13 '21 at 05:20
  • I am thinking of the following option for 11g. Can you suggest the better one? 1. Create a stored procedure with inner select. 2. Use ROWNUM in the query. 3. Is it possible to use RowMapper? If so, consider this also an alternative. – user1862354 Apr 13 '21 at 05:46
  • I'd prefer to use the proper SQL in a `@Query` annotation and pass `limit` and `offset` as parameters. Possibly adding a default method calling the first one and taking a `Pageable` as argument. – Jens Schauder Apr 13 '21 at 06:15