CODEWITHSUNDEEP
javahibernateoracle11gspring-data-jpaoracle12c
Shankar
Shankar

Reputation: 2835

JPA generated SQL with "fetch first ? rows only" fails with ORA-00933 in Oracle 11g

In a Spring Boot 2.0 project, I have a JpaRepository object with query methods. One query method returns a paginated resultset.

Page<Model> findByPartContainingIgnoreCaseAndModelContainingIgnoreCaseAndNeedsUpdateContainingIgnoreCase(String part, String model, String needsUpdate, Pageable pageReguest);

This JPA query method runs when querying a Oracle 12c database, with the following SQL:

select * from (SQL)  where rownum <= 100

The same JPA query method fails with (ORA-00933) when querying a Oracle 11g database, with the following SQL generated:

select SQL **fetch first 100 rows only**

Why does the same code base generate different SQLs for the different Oracle databases? How can we fix this?

The project uses the ojdbc6 (version - 11.2.0.4) Oracle Driver.

Upvotes: 1

Views: 4521

Answers (1)

Shankar
Shankar

Reputation: 2835

The issue was because of the dialect. I had used an older dialect at first - "org.hibernate.dialect.Oracle9iDialect"

The problem was resolved by setting the following dialect in application.properties.

spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

Upvotes: 3

Related Questions