R2dbc oracle ref cursor

[Islombek Nazirbekov]

Hi. I need to extract data from Oracle procedure which returns ref cursor. I tried more times, but didn't get the successful result. Please help me.

return databaseClient.sql("{call accounts.getAccountsByClientId(:p_branch, :p_clientid, :cur_out)} ")
.bind("p_branch", "00029")
.bind("p_clientid", "94502000200280093001")
.bind("cur_out", Account.class)
.map((row, rowData) -> {
Account account = new Account(
row.get("branch").toString(),
row.get("id").toString(),
row.get("client").toString(),
row.get("name").toString());
return account;
}).all();
}

This is my code. After that I get error

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]
    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.22.jar:5.3.22]
    at org.springframework.r2dbc.core.binding.NamedBindMarkers$NamedBindMarker.bind(NamedBindMarkers.java:102) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at org.springframework.r2dbc.core.NamedParameterUtils$ExpandedQuery.bind(NamedParameterUtils.java:538) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at org.springframework.r2dbc.core.NamedParameterUtils$ExpandedQuery.bindTo(NamedParameterUtils.java:587) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$execute$2(DefaultDatabaseClient.java:357) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$execute$3(DefaultDatabaseClient.java:374) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:46) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:31) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at org.springframework.r2dbc.core.DefaultFetchSpec.lambda$all$2(DefaultFetchSpec.java:88) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:46) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at org.springframework.r2dbc.core.ConnectionFunction.apply(ConnectionFunction.java:31) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$6(DefaultDatabaseClient.java:138) ~[spring-r2dbc-5.3.22.jar:5.3.22]
    at reactor.core.publisher.FluxUsingWhen.deriveFluxFromResource(FluxUsingWhen.java:120) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.FluxUsingWhen.access$000(FluxUsingWhen.java:54) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.FluxUsingWhen$ResourceSubscriber.onNext(FluxUsingWhen.java:193) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:122) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.FluxRetry$RetrySubscriber.onNext(FluxRetry.java:87) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1816) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoFlatMap$FlatMapInner.onNext(MonoFlatMap.java:249) ~[reactor-core-3.4.22.jar:3.4.22]
    at io.r2dbc.pool.MonoDiscardOnCancel$MonoDiscardOnCancelSubscriber.onNext(MonoDiscardOnCancel.java:92) ~[r2dbc-pool-0.9.1.RELEASE.jar:0.9.1.RELEASE]
    at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.complete(MonoIgnoreThen.java:292) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onNext(MonoIgnoreThen.java:187) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.complete(MonoIgnoreThen.java:292) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onNext(MonoIgnoreThen.java:187) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:236) ~[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.MonoIgnoreElements$IgnoreElementsSubscriber.onComplete(MonoIgnoreElements.java:89) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.FluxHandle$HandleSubscriber.onComplete(FluxHandle.java:220) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1817) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoCacheTime$CoordinatorSubscriber.signalCached(MonoCacheTime.java:337) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoCacheTime$CoordinatorSubscriber.onNext(MonoCacheTime.java:354) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.Operators$ScalarSubscription.request(Operators.java:2398) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoCacheTime$CoordinatorSubscriber.onSubscribe(MonoCacheTime.java:293) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoJust.subscribe(MonoJust.java:55) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:52) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoCacheTime.subscribeOrReturn(MonoCacheTime.java:143) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.Mono.subscribe(Mono.java:4382) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:263) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:240) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64) ~[reactor-core-3.4.22.jar:3.4.22]
    at io.r2dbc.pool.MonoDiscardOnCancel.subscribe(MonoDiscardOnCancel.java:50) ~[r2dbc-pool-0.9.1.RELEASE.jar:0.9.1.RELEASE]
    at reactor.core.publisher.MonoFlatMap$FlatMapMain.onNext(MonoFlatMap.java:157) ~[reactor-core-3.4.22.jar:3.4.22]
    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.22.jar:3.4.22]
    at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1816) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoFlatMap$FlatMapInner.onNext(MonoFlatMap.java:249) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.complete(MonoIgnoreThen.java:292) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onNext(MonoIgnoreThen.java:187) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:236) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoFlatMap$FlatMapMain.onNext(MonoFlatMap.java:157) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoNext$NextSubscriber.onNext(MonoNext.java:82) ~[reactor-core-3.4.22.jar:3.4.22]
    at reactor.core.publisher.MonoNext$NextSubscriber.onNext(MonoNext.java:82) ~[reactor-core-3.4.22.jar:3.4.22]
    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.22.jar:3.4.22]
    at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79) ~[reactor-core-3.4.22.jar:3.4.22]
    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: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.postFire(CompletableFuture.java:614) ~[na:na]
    at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:844) ~[na:na]
    at java.base/java.util.concurrent.CompletableFuture$Completion.exec(CompletableFuture.java:483) ~[na:na]
    at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:373) ~[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]

[Michael McMahon]

Hi there. I don't think I've tested support for REF cursors with Oracle R2DBC. This seems like a great thing to add, so I'll look into it soon.

Thank you for bringing this to my attention!

[bubeshkumar d]

We are exploring to call oracle stored procedure which returns ref cursor from spring webflux r2dbc application. 

Oracle R2DBC v1.1.0 has support of ref cursor but I dont find any examples on this. Can anyone share example of this? 

https://github.com/oracle/oracle-r2dbc/releases 

[Michael McMahon]

I have some basic examples for REF CURSOR in the README.md:

https://github.com/oracle/oracle-r2dbc?tab=readme-ov-file#ref-cursor

Hope this helps.