I'd like to switch from Apache Tomcat datasources to UCP.
With Tomcat datasources we use
Datasource.Initsql=ALTER SESSION SET CURRENT_SCHEMA=XXXXXXX
Datasource.tomcat.jdbcInterceptors=ConnectionPoolLogger(MODULE=XXXX, ACTION=YYYY)
Are there ways to do the same with UCP? Something like:
PoolDataSource.setModule('XXXX')
PoolDataSource.setAction('YYYY')
PoolDataSource.initSQL('ALTER SESSION SET CURRENT_SCHEMA=XXXXXXX')
We are using Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
From MOS note 2963891.1
You can't configure the default schema in UCP connection. However, you can configure the the default schema by executing the below query after connecting to the DB.
ALTER SESSION SET CURRENT_SCHEMA=Schema_Name;
Additionally, you can create a logon trigger on the user and/or database that will run this when a particular user logs in.Sorry...
One "hack" I've seen mentioned is hijacking the validation mechanism, eg
poolDataSource.setValidateConnectionOnBorrow(true);
poolDataSource.setSQLForValidateConnection("ALTER SESSION SET CURRENT_SCHEMA=MYSCHEMA")but the docs do not recommend that as a standard practice
"The setSQLForValidateConnection property is not recommended when using an Oracle JDBC driver. UCP performs an internal ping when using an Oracle JDBC driver. The mechanism is faster than executing a SQL statement, and is overridden if this property is set. Instead, set the setValidateConnectionOnBorrow property to true and do not include the setSQLForValidateConnection property."