Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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