Bad SQL grammar - Spring data R2DBC with Oracle

user_1908098 0
Viewed 48

I'm using Spring Data R2DBC with Oracle 11g and I have the following error using method findById of 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>
spring-data-r2dbc r2dbc oracle
3 Answers

I don't know anything about products you tagged (except Oracle) so I don't know whether this will help or not. Hopefully, it will).

FETCH FIRST 2 ROWS ONLY is added to the query and it's the cause of problem.

That's because - I presume - your Oracle database version doesn't support that syntax. I guess it is 11g or lower. See a demo:

Oracle 11g:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> SELECT *
  2    FROM emp
  3   WHERE deptno = 10
  4   FETCH FIRST 2 ROWS ONLY;
 FETCH FIRST 2 ROWS ONLY
 *
ERROR at line 4:
ORA-00933: SQL command not properly ended         --> that's your "bad grammar"

Oracle 18c:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL> SELECT *
  2    FROM emp
  3   WHERE deptno = 10
  4   FETCH FIRST 2 ROWS ONLY;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7839 KING       PRESIDENT            17.11.81       5000                    10

SQL>

So, what can you do? Rewrite the query (if possible). For example:

SQL> WITH
  2     temp
  3     AS
  4        (SELECT e.*, ROW_NUMBER () OVER (ORDER BY NULL) rn
  5           FROM emp e
  6          WHERE deptno = 10)
  7  SELECT *
  8    FROM temp
  9   WHERE rn <= 2;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.81       2818          1         10          1
      7839 KING       PRESIDENT            17.11.81       5750          1         10          2

SQL>

(Note that - without the ORDER BY clause - you can't really know which rows will be returned. If you're happy with any two rows, then OK. If not, include ORDER BY).

user_90979060

I think Littlefoot did a great job explaining why this is causing problems for you. Let me explain why that clause is there in the first place.

selectOne has the following contract:

Execute a SELECT query and convert the resulting item to an entity ensuring exactly one result.

This means it needs to try to fetch at least 2 elements to ensure that there actually isn't a second element. This could be done by selecting all matching rows. But it would never look at anything beyond the second row, so all the other rows would be selected in vain. And possibly worse, the execution plan might differ significantly when the database tries to load all rows vs when it only tries to fetch the first few.

Therefore the limit is applied to the query.

It uses the syntax that works for later versions of Oracle because those are the only ones supported by Spring Data JDBC.

You could avoid the problem by creating and registering your own Dialect that does not support any kind of limiting or paging and replaces ist with a noop. Of course you need to make sure that you don't try to use such features in your application, since they obviously won't work. Due to the interesting syntax required by older Oracle versions to implement this correctly it is not easily possible to properly implement paging and limiting for older Oracle versions.

The better option would be to migrate to a recent version of Oracle, although it is understood, that this often isn't easy for a variety of reasons.

Of course you always have the alternative to create your SQL statements in your code using either @Query annotations or custom methods.

Jens_Schauder_666860

If you're willing to use a third party library, you could translate the Oracle 12c compatible SQL using FETCH FIRST to Oracle 11g compatible SQL using jOOQ's ParsingConnection, which acts like an R2DBC Connection proxy, translating all incoming SQL to the target dialect. On Oracle 11g, the FETCH clause will be generated via ROWNUM filtering.

You could obviously also replace your Spring Data usage by jOOQ API usage to get the correct behaviour automatically. jOOQ supports R2DBC out of the box.

If that's not an option, then you'll have to write your SQL manually instead of relying on the DSL API of Spring Data or other third parties.

Disclaimer: I work for the company behind jOOQ.

user_5217990
Related