Bad SQL grammar - Spring data R2DBC with Oracle

[alessiofiore]

I'm using Spring Data R2DBC with Oracle 11g and I have the following error using method findByIdof R2dbcCrudRepository

executeMany; bad SQL grammar [SELECT GAME_PHASE.* FROM GAME_PHASE WHERE GAME_PHASE.ID = :P0_id FETCH FIRST 2 ROWS ONLY]

This is the repository declaration

public interface ReactiveGamePhaseRepository extends R2dbcRepository<GamePhase, Long> {
}

I don't understand why FETCH FIRST 2 ROWS ONLY is added to the query and it's the cause of problem.

I have the same problem writing the query using R2dbcEntityTemplate like below:

r2dbcEntityTemplate.selectOne(query(where("id").is(id)), GamePhase.class);

And these are the used dependancies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-r2dbc</artifactId>
    <version>2.5.12</version>
</dependency>

<dependency>
    <groupId>com.oracle.database.r2dbc</groupId>
    <artifactId>oracle-r2dbc</artifactId>
    <version>1.0.0</version>
</dependency>

[Michael-A-McMahon]

I believe the "FETCH FIRST" clause isn't supported by Oracle 11g (But don't take my word for it, please check the Oracle Database documentation to confirm). If I'm right, then this would explain in the grammar exception.

Oracle R2DBC is not generating this SQL. My guess is that the Spring component would be doing this. Please check with the Spring developers about this.

[mp911de]

Spring Data uses ANSI-style limit/offset clauses; See AnsiDialect

If you're using a database version that doesn't support that style, please subclass the OracleDialect and provide a LimitClause implementation that uses a syntax that is supported.

[mp911de]

Duplicate of https://github.com/spring-projects/spring-data-r2dbc/issues/782

[Michael-A-McMahon]

I hope this discussion was helpful. I'm going to close the issue as it seem to be a known issue that the Spring team is working on.