Programatically set v$session program property

Question

I found some answers on the problem but none that i could make work in my case. My problem is that I load a datasource from my JBoss configuration with spring:

<xa-datasource jndi-name="java:jboss/jdbc/oracleDatasource" pool-name="jdbc/oracleDatasource" enabled="true">
                <xa-datasource-property name="URL">
                    jdbc:oracle:thin:@URL:1522:SID
                </xa-datasource-property>
                <xa-datasource-property name="connectionProperties">
                    v$session.program=MyAPP
                </xa-datasource-property>
                <driver>oracle-jdbc</driver>

The spring loading is made as follows:

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="java:jboss/jdbc/oracleDatasource"/>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
     <constructor-arg ref="dataSource"/>
</bean>

As you can see, I have set the v$session.program property in JBoss, it works well.

The problem is that i have several applications (war) that can be deployed on the same JBoss server, using this configuration. What I want to do in this case is to have each of my application to have its own name written in the v$session.program property.

So basically, i would like to be able to load the same datasource on each app but to have each of them using its own name to log the program property in oracle DB. Is it possible or do I have to have one datasource for each application hosted?

Would putting information into `v$session.module` or `v$session.client_info` be an alternative? — , Feb 03 '17 at 15:53
Yes it would be a viable alternative (the field client_info is not used) but i'm facing the same issue with this one, i don't know how to set this property after the datasource has been loaded. As the connection is automatically created with the jdbcTemplate call, i cant modify its properties on connection creation as it is explained in this post : http://stackoverflow.com/questions/2876956/setting-client-info-in-jdbc-for-oracle. — user3218683, Feb 03 '17 at 16:45

score 0 · Answer 1 · answered Feb 03 '17 at 16:52

If putting this information into v$session.module or v$session.client_info is an option, you can do using Java code.

All you need to do is call dbms_application_info.set_module() or dbms_application_info.set_client_info() after your Java code obtained the connection from the datasource.

Something like this:

Connection conn = ... // get connection from the DataSource
CallableStatement cstmt = conn.prepareCall("{call dbms_application_info.set_client_info(?)}");
cstmt.setString(1, "Some interesting information");
cstmt.execute();

This is the PL/SQL approach, to be used only in case that the JDBC solution fails to work for a specific JDBC driver and RDBMS version — Marmite Bomber, Feb 03 '17 at 21:26

score 0 · Accepted Answer · edited May 23 '17 at 12:01

0

The only thing you need it to intercept each call of getConnection from connection pool.

You must obtain a real Oracle connection - not a proxy - and call the setClientInfo on 12c or setEndToEndMetrics in older versions to set the action / client / module identification.

An example see here.

Also note that the use of dbms_application_info for this same purpose works as well, but this produces a one server roundtrip too much. The setClientInfo doesn't produce server call, but stores this information for the next statement execution (which is the preformance saving approach).

Also note that to use this feature, your driver must match perfectly with your database - the strange exeptions you can see while setting teh client info are in most cases caused by the incompatibility of teh JDBC driver and the RDBMS.

edited May 23 '17 at 12:01

Community

1
1

answered Feb 03 '17 at 21:24

Marmite Bomber

19,886
4
26
53

Thank you for your reply, i tried to get the real connection but got stuck in a case because of the framework creating its own conneciton. I used the dmbs_application_info, it does the trick for me right now. I set an activation mode for this information so that i don't have the extra call for each connection but just on specific times, its enough for me. Thank you! – user3218683 Feb 13 '17 at 13:29
@user3218683, you are correct; each framework wraps up the connection in an own proxy object. You may check the javadoc for a method like *getConnection* that returns the original DB connection. And of course the DBMS_APPLICATION_INFO works fine, but - as said - cost one round-trip more. *Feel free to up-vote or accept the answer that you found useful:)* – Marmite Bomber Feb 13 '17 at 22:27

Programatically set v$session program property

2 Answers2

Linked