Oracle XE (11g) Pagination Fails. Cause: offset fetch fails with error: command not properly ended

Hibernate ORM
1

I’m using spring boot 2.0.5.RELEASE and spring data jpa.

I want to use Pageable to control pagination.

The generated SQL is only valid for Oracle 12, not XE/Oracle 11.

Is this a known Hibernate issue? and is there a workaround? Thanks.

Maven:


<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.5.RELEASE</version>
</parent>

Repository:

public List<Simple> findAll(Pageable pageable);

Spring Test Class:

list = simpleRepository.findAll(PageRequest.of(1, 1));
assertEquals(1, list.size());

Simplified SQL generated:

SELECT * FROM simple OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
2

You need to use the Oracle10gDialect not the Oracle12 one.

3

Thanks Vlad.

Adding this fixed the issue.
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect