Support RETURNING * INTO

[pull-vert]

Hello, I would like to do a simple R2DBC INSERT statement that returns all inserted columns like this (id is a auto-generated IDENTITY here), without PL/SQL

INSERT INTO OracleInts (intNotNull, intNullable) VALUES (?, ?) RETURNING id, intNotNull, intNullable INTO ?, ?, ?

With Oracle + JDBC (ojdbc8 or ojdbc11 works), It was working fine by calling registerReturnParameter after unwraping the OraclePreparedStatement

val oracleStatement = preparedStatement.unwrap(OraclePreparedStatement::class.java)
// ...
oracleStatement.registerReturnParameter(index, jdbcType)

Is there any solution to make it work with oracle-r2dbc ?

I tried statement.bind(index, Parameters.out(r2dbcType)) but this is not the same use case, so that does not work.

[Michael-A-McMahon]

Sorry for so long with no response. (Somehow, I missed the notification for this issue.)

Currently, SQL with a RETURNING clause is not supported by Oracle R2DBC. This stems from the Oracle JDBC driver, which seems to be requiring a call to registerReturnParameter when it detects the RETURNING clause in a our SQL.

For this case, I would recommend using Statement.returnGeneratedValues(String..), like this:

import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactories;
import reactor.core.publisher.Flux;

import java.time.Duration;

public class Test {

  public static void main(String[] args) {
    Flux.usingWhen(

      ConnectionFactories.get(
        "r2dbc:oracle://test?TNS_ADMIN=/users/micmcmah/.oracle/database")
        .create(),

      connection ->
        Flux.concatDelayError(

          connection.createStatement(
            "CREATE TABLE test (" +
              "id NUMBER GENERATED ALWAYS AS IDENTITY," +
              " intNotNull NUMBER NOT NULL," +
              " intNullable NUMBER)")
            .execute(),

          Flux.from(connection.createStatement(
            "INSERT INTO test (intNotNull, intNullable) VALUES (?, ?)")
            .bind(0, 9)
            .bindNull(1, Integer.class)
            .returnGeneratedValues("id")
            .execute())
            .flatMap(result ->
              result.map(readable ->
                readable.get(0, Integer.class)))
            .doOnNext(id -> System.out.println("Generated ID is: " + id)),

          connection.createStatement("DROP TABLE test")
            .execute()
        ),
      Connection::close)
      .blockLast(Duration.ofSeconds(30));
  }
}

Oracle R2DBC implements returningGeneratedValues to prepare a JDBC statement with "auto-generated keys": https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Connection.html#prepareStatement(java.lang.String,java.lang.String[])

Oracle JDBC implements prepareStatement(String, String[]) to generate the RETURNING clause for the INSERT statement. In the example above, this generated SQL is executed, and it similar to what you originally had:

INSERT INTO test (intNotNull, intNullable) VALUES (?, ?) RETURNING id, intNotNull, intNullable INTO ?, ?, ?

Do you think the returningGeneratedKeys method will provide the solution you need? If not, then I can look further into how we can add support for the RETURNING clause in Oracle R2DBC.

[pull-vert]

Hello @Michael-A-McMahon

Thanks a lot for your answer, I now use this returnGeneratedValues that is a perfectly acceptable solution for me !