Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ken.

Asked: October 29, 2024 - 11:44 am UTC

Last updated: November 11, 2024 - 9:36 am UTC

Version: 19.0.0.0.0

Viewed 100+ times

You Asked

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

and Connor said...

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."

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library