How do you set the current schema?

[JamesMcMahon]

In the samples you provide it looks like it commands are operating off the default schema (or it's set in a way I can't divine).

Can you provide an example on how to change the schema?

[Michael-A-McMahon]

The current schema is that of the database user which R2DBC is configured to authenticate as. Code in the samples/ directory is reading the user name from a file named "config.properties" in the current working directory. https://github.com/oracle/oracle-r2dbc/blob/938b999954b7fea1c4e4952df1994a965f31eed2/sample/src/main/java/oracle/r2dbc/samples/DatabaseConfig.java#L34

There's no equivalent to JDBC's setSchema in the R2DBC SPI

But if you'd like to change the schema after authenticating as a different user, then executing an ALTER SESSION command using io.r2dbc.spi.Statement should work.

alter session set current_schema = "scott"

This would actually be a non-blocking/reactive equivalent to how Oracle JDBC implements Connection.setSchema(String). Oracle JDBC just executes an ALTER SESSION command using a java.sql.Statement.

[JamesMcMahon]

That is helpful thanks!

My team was migrated from JDBC and wondering how to replace:

spring.datasource.hikari.connection-init-sql=ALTER SESSION SET CURRENT_SCHEMA=schemaname

In our properties file. That may be a concern of R2DBC-pool.

Our workaround was to specify the schema name in from our Spring Data @Table annotations. IE, @Table("schemaname.tablename"). Didn't feel particularly eloquent.

[Michael-A-McMahon]

Glad to hear this has helped :)

And yes, I think R2DBC-Pool might be the right place to have ALTER SESSION executed upon borrowing a connection.

I'll close this issue as it seems we have a resolution.