Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

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

We're not taking comments currently, so please try again later if you want to add 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 latest video and Chris's latest video 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