search

oracle / oracle-r2dbc

R2DBC Driver for Oracle Database
https://oracle.com
Other
197 stars 40 forks source link

Caused by: io.r2dbc.spi.R2dbcBadGrammarException: ORA-00923 #96

Closed 18356555003 closed 2 years ago

18356555003 commented 2 years ago

spring.application.name=test-r2dbc spring.r2dbc.url=r2dbc:oracle:thin://localhost:1521:orcl spring.r2dbc.username=c##test spring.r2dbc.password=123123 spring.r2dbc.pool.max-size=100 spring.r2dbc.pool.max-idle-time=2m spring.r2dbc.pool.initial-size=100 spring.r2dbc.pool.validation-query=select 1

public interface TestDao extends ReactiveCrudRepository<Test,String> {

}

@RestController @RequestMapping("/test") public class TestController { private final TestDao testDao;

  public TestController(TestDao testDao) {
      this.testDao = testDao;
  }
  @GetMapping("/test")
  public Flux<Test> getAll(){
      return testDao.findAll();
  }

}

org.springframework.dao.DataAccessResourceFailureException: Failed to obtain R2DBC Connection; nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [923] [42000] ORA-00923: 未找到要求的 FROM 关键字

at org.springframework.r2dbc.connection.ConnectionFactoryUtils.lambda$getConnection$0(ConnectionFactoryUtils.java:88) ~[spring-r2dbc-5.3.22.jar:5.3.22]
Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException:

Error has been observed at the following site(s): __checkpoint ⇢ Handler com.example.testr2dbc.controller.TestController#getAllList() [DispatcherHandler] __checkpoint ⇢ HTTP GET "/test/test" [ExceptionHandlingWebHandler] Original Stack Trace: at org.springframework.r2dbc.connection.ConnectionFactoryUtils.lambda$getConnection$0(ConnectionFactoryUtils.java:88) ~[spring-r2dbc-5.3.22.jar:5.3.22] at reactor.core.publisher.Mono.lambda$onErrorMap$31(Mono.java:3730) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:94) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:106) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.FluxRetry$RetrySubscriber.onError(FluxRetry.java:95) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.MonoFlatMap$FlatMapMain.secondError(MonoFlatMap.java:192) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.MonoFlatMap$FlatMapInner.onError(MonoFlatMap.java:259) ~[reactor-core-3.4.22.jar:3.4.22] at io.r2dbc.pool.MonoDiscardOnCancel$MonoDiscardOnCancelSubscriber.onError(MonoDiscardOnCancel.java:98) ~[r2dbc-pool-0.9.0.RELEASE.jar:0.9.0.RELEASE] at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:106) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onError(MonoIgnoreThen.java:278) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:231) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:203) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.FluxDoFinally$DoFinallySubscriber.onComplete(FluxDoFinally.java:128) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.FluxDoFinally$DoFinallySubscriber.onComplete(FluxDoFinally.java:128) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.MonoNext$NextSubscriber.onComplete(MonoNext.java:102) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.MonoNext$NextSubscriber.onComplete(MonoNext.java:102) ~[reactor-core-3.4.22.jar:3.4.22] at oracle.r2dbc.impl.AsyncLock$UsingConnectionSubscriber.onComplete(AsyncLock.java:503) ~[oracle-r2dbc-1.0.0.jar:1.0.0] at reactor.core.publisher.StrictSubscriber.onComplete(StrictSubscriber.java:123) ~[reactor-core-3.4.22.jar:3.4.22] at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2058) ~[reactor-core-3.4.22.jar:3.4.22] at org.reactivestreams.FlowAdapters$FlowToReactiveSubscriber.onComplete(FlowAdapters.java:221) ~[reactive-streams-1.0.4.jar:na] at oracle.jdbc.internal.CompletionStageUtil$IteratorSubscription.emitComplete(CompletionStageUtil.java:805) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0] at oracle.jdbc.internal.CompletionStageUtil$BatchItemPublisher.subscribeToBatch(CompletionStageUtil.java:622) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0] at oracle.jdbc.internal.CompletionStageUtil$BatchItemPublisher.lambda$subscribe$0(CompletionStageUtil.java:593) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0] at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:863) ~[na:na] at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:841) ~[na:na] at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:510) ~[na:na] at java.base/java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:2147) ~[na:na] at oracle.jdbc.driver.T4CTTIfun.lambda$doRPCAsync$0(T4CTTIfun.java:348) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0] at oracle.jdbc.driver.T4CTTIfun.lambda$receiveRPCAsync$1(T4CTTIfun.java:474) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0] at oracle.jdbc.driver.RestrictedLock.lambda$runUnrestricted$0(RestrictedLock.java:428) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0] at oracle.jdbc.driver.RestrictedLock.callUnrestricted(RestrictedLock.java:447) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0] at oracle.jdbc.driver.RestrictedLock.runUnrestricted(RestrictedLock.java:427) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0] at oracle.jdbc.driver.PhysicalConnection.lambda$initializeAsyncExecutor$4(PhysicalConnection.java:1270) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0] at java.base/java.util.concurrent.ForkJoinTask$RunnableExecuteAction.exec(ForkJoinTask.java:1395) ~[na:na] at java.base/java.util.concurrent.ForkJoinTask.doExec$$$capture(ForkJoinTask.java:373) ~[na:na] at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java) ~[na:na] at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1182) ~[na:na] at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1655) ~[na:na] at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1622) ~[na:na] at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:165) ~[na:na] Caused by: io.r2dbc.spi.R2dbcBadGrammarException: ORA-00923: 未找到要求的 FROM 关键字

at oracle.r2dbc.impl.OracleR2dbcExceptions.toR2dbcException(OracleR2dbcExceptions.java:175) ~[oracle-r2dbc-1.0.0.jar:1.0.0]
at reactor.core.publisher.Flux.lambda$onErrorMap$29(Flux.java:6943) ~[reactor-core-3.4.22.jar:3.4.22]
at reactor.core.publisher.Flux.lambda$onErrorResume$30(Flux.java:6996) ~[reactor-core-3.4.22.jar:3.4.22]
at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:94) ~[reactor-core-3.4.22.jar:3.4.22]
at org.reactivestreams.FlowAdapters$FlowToReactiveSubscriber.onError(FlowAdapters.java:216) ~[reactive-streams-1.0.4.jar:na]
at oracle.jdbc.internal.CompletionStageUtil$BatchItemPublisher.subscribeToFailedBatch(CompletionStageUtil.java:636) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.internal.CompletionStageUtil$BatchItemPublisher.lambda$subscribe$0(CompletionStageUtil.java:591) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:863) ~[na:na]
at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:841) ~[na:na]
at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:510) ~[na:na]
at java.base/java.util.concurrent.CompletableFuture.completeExceptionally(CompletableFuture.java:2162) ~[na:na]
at oracle.jdbc.driver.OraclePreparedStatement.lambda$executeAsyncOracle$4(OraclePreparedStatement.java:4171) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OraclePreparedStatement.lambda$executeInternalAsync$0(OraclePreparedStatement.java:3814) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OracleStatement.lambda$doExecuteWithTimeoutAsync$2(OracleStatement.java:1556) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OracleStatement.lambda$executeSQLSelectAsync$3(OracleStatement.java:1727) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OracleStatement.lambda$executeMaybeDescribeAsync$0(OracleStatement.java:1211) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.OracleStatement.lambda$prepareDefineBufferAndExecuteAsync$1(OracleStatement.java:1348) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CPreparedStatement.lambda$executeForDescribeAsync$2(T4CPreparedStatement.java:1109) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CPreparedStatement.lambda$doOall8Async$0(T4CPreparedStatement.java:236) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4C8Oall.lambda$doOALLAsync$0(T4C8Oall.java:583) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.lambda$receiveRPCAsync$1(T4CTTIfun.java:474) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.RestrictedLock.lambda$runUnrestricted$0(RestrictedLock.java:428) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.RestrictedLock.callUnrestricted(RestrictedLock.java:447) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.RestrictedLock.runUnrestricted(RestrictedLock.java:427) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.PhysicalConnection.lambda$initializeAsyncExecutor$4(PhysicalConnection.java:1270) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at java.base/java.util.concurrent.ForkJoinTask$RunnableExecuteAction.exec(ForkJoinTask.java:1395) ~[na:na]
at java.base/java.util.concurrent.ForkJoinTask.doExec$$$capture(ForkJoinTask.java:373) ~[na:na]
at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java) ~[na:na]
at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1182) ~[na:na]
at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1655) ~[na:na]
at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1622) ~[na:na]
at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:165) ~[na:na]

Caused by: java.sql.SQLSyntaxErrorException: ORA-00923: 未找到要求的 FROM 关键字

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1231) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:772) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
at oracle.jdbc.driver.T4CTTIfun.lambda$receiveRPCAsync$1(T4CTTIfun.java:441) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
... 11 common frames omitted

Caused by: oracle.jdbc.OracleDatabaseException: ORA-00923: 未找到要求的 FROM 关键字

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637) ~[ojdbc11-21.5.0.0.jar:21.5.0.0.0]
... 15 common frames omitted
Michael-A-McMahon commented 2 years ago

Hi @18356555003

Oracle Database is reporting that it can not recognize the following as valid SQL:

未找到要求的 FROM 关键字

I only know English, so I can't discern much of the non-English characters. Regardless, I think these should serve as valid names for columns and tables.

I believe there should be SELECT or DELETE keyword at the beginning. If we don't have that, then the database will report ORA-00923.

It looks like you are using Spring to generate this SQL. I think you may want to check with that team about this case.

I hope this helps!

18356555003 commented 2 years ago

Thanks @Michael-A-McMahon The characters is Chinese... The same code run success with MySQL ，but fail with oracle. My project : Spring-r2dbc + oracle-r2dbc

Michael-A-McMahon commented 2 years ago

Have you checked with the Spring R2DBC team?

I think the SQL should be:

SELECT 未找到要求的 FROM 关键字

But Spring is generating SQL without the SELECT:

未找到要求的 FROM 关键字

@mp911de: Any idea about this?

mp911de commented 2 years ago

I'm actually wondering where this problem comes from. In any case, this is the wrong issue tracker when it comes to SQL generation, @18356555003 please file a bug report in https://github.com/spring-projects/spring-data-relational along with a complete reproducer (entity, repository). It would be helpful to also enable debug logging for the org.springframework.r2dbc category to see what SQL is being executed.

Michael-A-McMahon commented 2 years ago

Thanks for the assist, @mp911de! I'll close this issue as it does not seem to be caused by Oracle R2DBC.