Configure JDBC oracle specific property v$session.program using Jboss and JPA (hibernate)

Question

I'd like to set the v$session.program Oracle property in order to have information available in the session table. I'm using JPA with a jndi XA datasource created with an oracle-xa-ds.xml deployed in the deploy folder of Jboss, and therefore I haven't access to the constructor of the Connection. I have access to the Connection object, in JPA 2 using unwrap, in JPA 1 by casting JPA to Hibernate classes, but there are no properties setter (only Client Info properties that are the way to proceed starting JDBC 4.0).

So my question is, using JPA (with Hibernate) using Jboss 4.2 :

• Is it possible to configure the v$session.program in the persistence.xml ?
• Is it possible to configure the v$session.program in the oracle-ds.xml ?
• Is their any other approach to the solution ?

Thank you for any valuable comments and answers !

Answer 1

I had the same Problem today, after much fiddeling and reading documentation finally I had the Eureka moment:

Add following parameter:

<xa-datasource-property name="connectionProperties">v$session.program=YourUniqueName</xa-datasource-property>

Thats all.

Answer 2

I'm pretty sure this must be documented somewhere but here is what we can find in the JBoss wiki:

How To Specify "PROGRAM" Oracle Connection Property

JBoss Version: JBoss 4.0.3 SP1, Oracle DB Version: 10g

To be able to distinguish the JDBC connections on the Oracle server side, which are created by different JBoss instances, Oracle's PROGRAM connection property might be set within the Oracle specific JDBC datasource config file by using the following tags:

<connection-property name="v$session.program">ADistinguishedNameForPROGRAMProperty</connection-property>

i.e.

...
<connection-url>AConnectionURL</connection-url>
<connection-property name="v$session.program">ADistinguishedNameForPROGRAMProperty</connection-property>
<driver-class>oracle.jdbc.OracleDriver</driver-class>
...

That way the DBAs can have proper granularity in:

• AWRs
• v$session view
• Other tools which are checking/evaluating PROGRAM connection property