search

oracle / oracle-r2dbc

R2DBC Driver for Oracle Database
https://oracle.com
Other
194 stars 40 forks source link

Allow to specify v$session.program for connection #66

Closed joao-rebelo closed 2 years ago

joao-rebelo commented 2 years ago

We need a way to set the v$session.program at the connection from a R2DBC Oracle connection and after reviewing documentation and stack overflow I haven't found a way to do it (my case is within a Spring application, but I believe this to be irrelevant)

We've added an issue at StackOverflow without answers: https://stackoverflow.com/questions/72048278/specify-vsession-program-for-r2dbc-oracle-connection

We've tried some solutions including this but without success.

ConnectionFactories.get( ConnectionFactoryOptions.parse(dbUrl) .mutate() .option(Option.valueOf("v$session.program"), "PROGRAMNAME") .build());

After reviewing the driver source code, it seems the property would need to be supported at the OracleReactiveJdbcAdapter::JDBC_CONNECTION_PROPERTY_OPTIONS Set but any other way to achieve the same goal would be useful.

If this is already possible please excuse me, but I haven't found one.

Michael-A-McMahon commented 2 years ago

Thanks for bringing this to my attention. You're correct that JDBC_CONNECTION_PROPERTY_OPTIONS is filtering the option, so there's no way to do this with currently (at least not with ConnectionFactoryOptions or r2dbc: URL parameters).

Since the v$session properties are important for tracing, I think that Oracle R2DBC should allow them. I may have time in the next week to make this change.

As a temporary work around, the v$session.program property could be set using JVM system properties.

joao-rebelo commented 2 years ago

thanks for the hint. For the workaround would it be to set the property: -Doracle.jdbc.v$session.program=PROGRAMNAME If so I've tried and it didn't worked.

Or should we set something diferent (any documentation I can refer to)?

Michael-A-McMahon commented 2 years ago

I'm surprised that doesn't work. I can see code in Oracle JDBC that effectively calls: System.getProperty("oracle.jdbc.v$session.program")

I wonder what's going wrong? If you can get it working, let me know. Otherwise, I'll have to find time to debug this later.

One more thing you could try is using an ojdbc.properties file. https://docs.oracle.com/en/database/oracle/oracle-database/21/jajdb/oracle/jdbc/OracleConnection.html#CONNECTION_PROPERTY_CONFIG_FILE Oracle JDBC will search for this file under a TNS_ADMIN directory.

joao-rebelo commented 2 years ago

I was now able to confirm that setting the -Doracle.jdbc.v$session.program=PROGRAMNAME worked as expected. The sessions I was testing with were to short for me to get them at the v$session and for them to go to the sample tables.. For my purpose this is enough as we don't need to set it programatically

Michael-A-McMahon commented 2 years ago

Glad to hear it's working now. We can leave this issue open as I still plan to add support for ConnectionFactoryOptions.

Thanks for looking into this with me!