Pageable functionality does not work for Oracle Pre-12c

[aaraujo666]

Expected Behavior

I expected to be able to use Pageable against the Oracle database

Actual Behaviour

The queries created by Micronaut return "SQL Command not properly ended".

Pageable functionality does not exist in Oracle's SQL implementation prior to version 12c.

Oracle does not support "LIMIT X,Y", nor "OFFSET X FETCH NEXT Y ROWS".

Steps To Reproduce

Create any PageableRepository against an Oracle database that is version 11 or lower.

Environment Information

No response

Example Application

No response

Version

latest

[dstepanov]

How would you implement it pre 12c?

[aaraujo666]

In 12c,

SELECT val FROM table ORDER BY val OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

The bolded above is what micronaut does when you select dialect Oracle.

To do the same on 11g and prior, you need to use ROWNUM twice, inner query and outer query respectively.

The same query in 11g,

SELECT val FROM (SELECT val, rownum AS rnum FROM (SELECT val FROM table ORDER BY val) WHERE rownum <= 8) WHERE rnum > 4;

Here OFFSET is 4.

This is how it needs to work in pre-12c... It's a pretty big change in the way the query is built, not like just tacking "OFFSET this FETCH NEXT that" on the end, otherwise I would have tried it myself and submitted a PR :)

[dstepanov]

Not sure if we should spend time supporting pre 12c

[dstepanov]

Going to close it as not planned.