Is read only mode supported?

[hughpearse]

Hello I am writing code using this library to run arbitrary SQL queries via a service account. I would like to build a connection in read only mode. I have tried the following ways and failed.

First attempt:

@Transactional
public Mono<String> runQueryStrictlyReadOnly() {
    return databaseClient.sql("SET TRANSACTION READ ONLY")
            .then()
            .then(databaseClient.sql("SELECT name FROM my_table WHERE id = 1")
                .map(row -> row.get("name", String.class))
                .one()
            );
}

Second attempt:

   @Transactional(readOnly = true)
   public Mono<String> runQuery() {
       return databaseClient.sql("SELECT name FROM my_table WHERE id = 1")
               .map(row -> row.get("name", String.class))
               .one();
   }
}

Third attempt:

ConnectionFactoryOptions options = ConnectionFactoryOptions.builder()
    .option(ConnectionFactoryOptions.DRIVER, "oracle")
    .option(ConnectionFactoryOptions.HOST, "localhost")
    .option(ConnectionFactoryOptions.PORT, 1521)
    .option(ConnectionFactoryOptions.USER, "read_user")
    .option(ConnectionFactoryOptions.PASSWORD, "password123")
    .option(Option.valueOf("readOnly"), true) 
    .build();

None of these block attempts to run insert statements.
Is it even possible?

[Michael-A-McMahon]

I'm able to get "ORA-01456: may not perform insert, delete, update operation inside a READ ONLY transaction" when executing an INSERT with this code:

import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.Option;
import io.r2dbc.spi.R2dbcException;
import io.r2dbc.spi.Result;
import io.r2dbc.spi.TransactionDefinition;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import java.util.Map;

public class ReadOnlyTransaction {

  public static void main(String[] args) throws Exception {
    ConnectionFactory connectionFactory =
      ConnectionFactories.get("r2dbc:oracle://test");

    Connection connection = Mono.from(connectionFactory.create()).block();
    try (
      AutoCloseable closeConnection = () ->
        Mono.from(connection.close()).block();
      AutoCloseable dropTable = () ->
        Mono.from(connection.createStatement("DROP TABLE test").execute())
          .map(Result::getRowsUpdated)
          .flatMap(Mono::from)
          .block()) {

      Mono.from(connection.createStatement(
        "CREATE TABLE test (id NUMBER PRIMARY KEY)")
        .execute())
        .map(Result::getRowsUpdated)
        .flatMap(Mono::from)
        .block();
      // Oracle Database has a known limitation in that table must exist for
      // at least 1 second before it can be involved in a READ ONLY transaction.
      // If this two-second sleep is not here, the result will be:
      // "ORA-01466: unable to read data - table definition has changed"
      Thread.sleep(2_000);

      Mono.from(connection.createStatement("INSERT INTO test VALUES(?)")
          .bind(0, 1)
          .execute())
        .map(Result::getRowsUpdated)
        .flatMap(Mono::from)
        .block();

      class TransactionDefinitionImpl implements TransactionDefinition {
        final Map<Option<?>, ?> attributes;

        TransactionDefinitionImpl(Map<Option<?>, ?> attributes) {
          this.attributes = attributes;
        }

        @Override
        public <T> T getAttribute(Option<T> option) {
          @SuppressWarnings("unchecked")
          T attribute = (T)attributes.get(option);
          return attribute;
        }
      }

      TransactionDefinition transactionDefinition =
        new TransactionDefinitionImpl(
          Map.of(TransactionDefinition.READ_ONLY, true));
      Mono.from(connection.beginTransaction(transactionDefinition))
        .block();

      Mono.from(connection.createStatement("SELECT id FROM test")
          .execute())
        .map(result -> result.map(row -> row.get(0)))
        .flatMapMany(Flux::from)
        .toStream()
        .forEach(id -> System.out.println("Queried ID: " + id));

      try {
        Mono.from(connection.createStatement("INSERT INTO test VALUES(?)")
            .bind(0, 2)
            .execute())
          .map(Result::getRowsUpdated)
          .flatMap(Mono::from)
          .block();
      }
      catch (R2dbcException r2dbcException) {
        // Expect:
        // ORA-01456: may not perform insert, delete, update operation inside a READ ONLY transaction
        System.out.println(r2dbcException.getMessage());
      }
    }
  }
}

Can you try this out with your DB? Note that this code will DROP a table named "test", so you may want to change the table name if you don't want that to happen.

[hughpearse]

ah sorry I was just missing this:

    @Bean(name = "OracleTransactionManager")
    public R2dbcTransactionManager transactionManager(@Qualifier("OracleConnectionPool") ConnectionPool connectionPool) {
        return new R2dbcTransactionManager(connectionPool);
    }