How to call a Stored Procedure from oracle using spring-data-r2dbc with OUT parameter as REF Cursor.I was trying to use the DatabaseClient with sql() method, but not able to map the out parameter with REF Cursor
pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-webflux</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-vault-config</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc11</artifactId>
<version>21.6.0.0.1</version>
</dependency>
<dependency>
<groupId>com.oracle.database.r2dbc</groupId>
<artifactId>oracle-r2dbc</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-r2dbc</artifactId>
</dependency>
<dependency>
<groupId>io.projectreactor</groupId>
<artifactId>reactor-test</artifactId>
<scope>test</scope>
</dependency>
application.yaml
r2dbc:
url: r2dbc:oracle:thin://${oracle.host}:${oracle.port}/${oracle.service-name}
username: ${username}
password: ${password}
pool:
enabled: true
initial-size: 5
max-size: 10
max-idle-time: 120s
max-life-time: 1500s
max-acquire-time: 5s
max-create-connection-time: 60s
Java Code
public Flux<?> findAllData (String input) {
return client.sql("CALL GET_DATA(:IN,:OUT)")
.bind("IN", input)
.bind("OUT", CustomClass.class)
.map((row, rowMetadata) -> {
<row conversion code>
}).fetch().all();
}
The following error is thrown when i run it
java.lang.IllegalArgumentException: Unsupported Java type:class java.lang.Class
at oracle.r2dbc.impl.OracleStatementImpl.requireSupportedJavaType(OracleStatementImpl.java:850) ~[oracle-r2dbc-1.0.0.jar:1.0.0]
Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException:
Error has been observed at the following site(s):
*__checkpoint ⇢ Handler com.mycomp.comp.metadata.controller.MetadataController#getAll() [DispatcherHandler]
*__checkpoint ⇢ org.springframework.security.web.server.authorization.AuthorizationWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.web.server.authorization.ExceptionTranslationWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.web.server.authentication.logout.LogoutWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.web.server.savedrequest.ServerRequestCacheWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.web.server.context.SecurityContextServerWebExchangeWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.web.server.ui.LogoutPageGeneratingWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.web.server.ui.LoginPageGeneratingWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.web.server.authentication.AuthenticationWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.oauth2.client.web.server.authentication.OAuth2LoginAuthenticationWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.oauth2.client.web.server.OAuth2AuthorizationRequestRedirectWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.web.server.context.ReactorContextWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.web.server.header.HttpHeaderWriterWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.config.web.server.ServerHttpSecurity$ServerWebExchangeReactorContextWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.security.web.server.WebFilterChainProxy [DefaultWebFilterChain]
*__checkpoint ⇢ org.springframework.boot.actuate.metrics.web.reactive.server.MetricsWebFilter [DefaultWebFilterChain]
*__checkpoint ⇢ HTTP POST "/metadata/all" [ExceptionHandlingWebHandler]
Original Stack Trace:
at oracle.r2dbc.impl.OracleStatementImpl.requireSupportedJavaType(OracleStatementImpl.java:850) ~[oracle-r2dbc-1.0.0.jar:1.0.0]
at oracle.r2dbc.impl.OracleStatementImpl.bindObject(OracleStatementImpl.java:706) ~[oracle-r2dbc-1.0.0.jar:1.0.0]
at oracle.r2dbc.impl.OracleStatementImpl.bindNamedParameter(OracleStatementImpl.java:667) ~[oracle-r2dbc-1.0.0.jar:1.0.0]
at oracle.r2dbc.impl.OracleStatementImpl.bind(OracleStatementImpl.java:306) ~[oracle-r2dbc-1.0.0.jar:1.0.0]
at org.springframework.r2dbc.core.DefaultDatabaseClient$StatementWrapper.bind(DefaultDatabaseClient.java:539) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.binding.NamedBindMarkers$NamedBindMarker.bind(NamedBindMarkers.java:102) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.NamedParameterUtils$ExpandedQuery.bind(NamedParameterUtils.java:538) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.NamedParameterUtils$ExpandedQuery.bindTo(NamedParameterUtils.java:587) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$execute$2(DefaultDatabaseClient.java:357) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$execute$3(DefaultDatabaseClient.java:374) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:46) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:31) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.DefaultFetchSpec.lambda$all$2(DefaultFetchSpec.java:88) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:46) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:31) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$6(DefaultDatabaseClient.java:138) ~[spring-r2dbc-5.3.21.jar:5.3.21]
at reactor.core.publisher.FluxUsingWhen.deriveFluxFromResource(FluxUsingWhen.java:120) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxUsingWhen.access$000(FluxUsingWhen.java:54) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxUsingWhen$ResourceSubscriber.onNext(FluxUsingWhen.java:193) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:122) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxRetry$RetrySubscriber.onNext(FluxRetry.java:87) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.SerializedSubscriber.onNext(SerializedSubscriber.java:99) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.SerializedSubscriber.onNext(SerializedSubscriber.java:99) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxTimeout$TimeoutMainSubscriber.onNext(FluxTimeout.java:180) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1816) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoFlatMap$FlatMapInner.onNext(MonoFlatMap.java:249) ~[reactor-core-3.4.19.jar:3.4.19]
at io.r2dbc.pool.MonoDiscardOnCancel$MonoDiscardOnCancelSubscriber.onNext(MonoDiscardOnCancel.java:92) ~[r2dbc-pool-0.9.0.RELEASE.jar:0.9.0.RELEASE]
at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.complete(MonoIgnoreThen.java:292) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onNext(MonoIgnoreThen.java:187) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.complete(MonoIgnoreThen.java:292) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onNext(MonoIgnoreThen.java:187) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:236) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:203) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2058) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.SerializedSubscriber.onComplete(SerializedSubscriber.java:146) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.SerializedSubscriber.onComplete(SerializedSubscriber.java:146) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxTimeout$TimeoutMainSubscriber.onComplete(FluxTimeout.java:234) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onComplete(MonoIgnoreElements.java:89) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxHandle$HandleSubscriber.onComplete(FluxHandle.java:220) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1817) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoCacheTime$CoordinatorSubscriber.signalCached(MonoCacheTime.java:337) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoCacheTime$CoordinatorSubscriber.onNext(MonoCacheTime.java:354) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.Operators$ScalarSubscription.request(Operators.java:2398) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoCacheTime$CoordinatorSubscriber.onSubscribe(MonoCacheTime.java:293) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoJust.subscribe(MonoJust.java:55) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:52) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoCacheTime.subscribeOrReturn(MonoCacheTime.java:143) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.Mono.subscribe(Mono.java:4382) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:263) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:240) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64) ~[reactor-core-3.4.19.jar:3.4.19]
at io.r2dbc.pool.MonoDiscardOnCancel.subscribe(MonoDiscardOnCancel.java:50) ~[r2dbc-pool-0.9.0.RELEASE.jar:0.9.0.RELEASE]
at reactor.core.publisher.MonoFlatMap$FlatMapMain.onNext(MonoFlatMap.java:157) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.pool.AbstractPool$Borrower.deliver(AbstractPool.java:467) ~[reactor-pool-0.2.9.jar:0.2.9]
at reactor.pool.SimpleDequePool.lambda$drainLoop$9(SimpleDequePool.java:423) ~[reactor-pool-0.2.9.jar:0.2.9]
at reactor.core.publisher.FluxDoOnEach$DoOnEachSubscriber.onNext(FluxDoOnEach.java:154) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.SerializedSubscriber.onNext(SerializedSubscriber.java:99) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.SerializedSubscriber.onNext(SerializedSubscriber.java:99) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxTimeout$TimeoutMainSubscriber.onNext(FluxTimeout.java:180) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1816) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoFlatMap$FlatMapInner.onNext(MonoFlatMap.java:249) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.complete(MonoIgnoreThen.java:292) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onNext(MonoIgnoreThen.java:187) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:236) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoFlatMap$FlatMapMain.onNext(MonoFlatMap.java:157) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoNext$NextSubscriber.onNext(MonoNext.java:82) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.MonoNext$NextSubscriber.onNext(MonoNext.java:82) ~[reactor-core-3.4.19.jar:3.4.19]
at oracle.r2dbc.impl.AsyncLock$UsingConnectionSubscriber.onNext(AsyncLock.java:461) ~[oracle-r2dbc-1.0.0.jar:1.0.0]
at reactor.core.publisher.StrictSubscriber.onNext(StrictSubscriber.java:89) ~[reactor-core-3.4.19.jar:3.4.19]
at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.19.jar:3.4.19]
at org.reactivestreams.FlowAdapters$FlowToReactiveSubscriber.onNext(FlowAdapters.java:211) ~[reactive-streams-1.0.4.jar:na]
at oracle.jdbc.datasource.impl.OracleDataSource$ConnectionPublisher$ConnectionSubscription.emitConnection(OracleDataSource.java:2895) ~[ojdbc11-21.6.0.0.1.jar:21.6.0.0.1]
at oracle.jdbc.datasource.impl.OracleDataSource$ConnectionPublisher$ConnectionSubscription.lambda$publishConnectionAsync$0(OracleDataSource.java:2882) ~[ojdbc11-21.6.0.0.1.jar:21.6.0.0.1]
at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859) ~[na:na]
at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:837) ~[na:na]
at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506) ~[na:na]
at java.base/java.util.concurrent.CompletableFuture.postFire(CompletableFuture.java:610) ~[na:na]
at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:840) ~[na:na]
at java.base/java.util.concurrent.CompletableFuture$Completion.exec(CompletableFuture.java:479) ~[na:na]
at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:290) ~[na:na]
at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1016) ~[na:na]
at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1665) ~[na:na]
at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1598) ~[na:na]
at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:183) ~[na:na]
The OUT parameter is a REF Cursor retuned from Oracle Stored Procedure with the type of CustomClass object