Oracle doesn't throw error when there is missing permission for the sequence while insertion

[rathoreamrsingh]

Steps to reproduce:

1. Create a table with sequence for ID in it.
2. Create different user.
3. Grant Select permission for the table to this newly created user but don't grant select permission on sequence.
4. Insert data to the table using below method.
   DatabaseClient dbclient = getDbClient();
   Flux<? extends Result> flux = dbclient.inConnectionMany(connection -> {
   Statement statement = connection.createStatement(insertStatement);
   bindData(recordsToSave, statement);
   return Flux.from(statement.execute())
   .doOnNext(data -> {
   log.info(data);
   })
   .doOnError(data -> {
   log.error(data);
   });
   });

This results in OracleResultImpl$BatchUpdateErrorResult which comes under doOnNext rather than doOnError.

The expectation is error should be in error stream doOnError but it is coming as data in doOnNext stream.

Please suggest how to resolve this issue or can be caught in proper manner.

[Michael-A-McMahon]

I can appreciate the confusion here: We might expect the Statement.execute() Publisher to emit onError when that statement fails. This is actually not what the Publisher is specified to do. The Publisher is specified to emit Result objects, and a Result may represent a database error.

For a batch update statement, you can add an operator which maps each Result to the count of rows updated by each set of bind values in your batch:

Flux.from(statement.execute())
  .flatMap(Result::getRowsUpdated)

The getRowsUpdated method will return a Publisher that emits the error to onError.

Hope this helps. Please let me know.

[rathoreamrsingh]

In this somehow the .flatMap(Result::getRowsUpdated) is getting called twice.
Second time since there is no bind with the statement it is throwing below mentioned error.

Caused by: oracle.r2dbc.impl.OracleR2dbcException$OracleR2dbcNonTransientException: One or more binds not set after calling add()

it is supposed to call this statement once.

[Michael-A-McMahon]

I think you'll be able to resolve this error by not calling add() after calling bind methods for the last set of binds. It is an awkward requirement, but we do this be compliant with the R2DBC Specification (r2dbc/r2dbc-spi#259).

Receiving two results here is something I may want to fix. The first result should be an update count, and the second result should be the error. This behavior is an artifact of an early version of the SPI compliance test (r2dbc/r2dbc-spi#259). I noticed that in the latest version, the requirement to return an update count is gone (https://github.com/r2dbc/r2dbc-spi/blob/fc07457586183f32bf88ca65ada62c819bc164e4/r2dbc-spi-test/src/main/java/io/r2dbc/spi/test/TestKit.java#L672). So there's really no need to do this anymore.