1. Create a data source as follows which is using the JDBC driver as the factory class (oracle.jdbc.driver.OracleDriver)
<managed-data-source
connection-pool-name="ScottConnectionPool"
jndi-name="jdbc/scottDS"
name="jdbc/scottDS"/>
<connection-pool
name="ScottConnectionPool"
initial-limit="5"
min-connections="5">
<connection-factory
factory-class="oracle.jdbc.driver.OracleDriver"
user="scott"
password="tiger"
url="jdbc:oracle:thin:@//papicell-au2.au.oracle.com:1521/lnx102">
<property name="v$session.program"
value="OAS10132-apple-scottpool"/>
</connection-factory>
</connection-pool>
2. In order to populate the PROGRAM column you need to set the property as shown below.
<property name="v$session.program"
value="OAS10132-apple-scottpool">
3. When the data source is started it will create 5 connections so we can then run the following SQL to see if it worked once the connection pool has been started.
set head on feedback on
set pages 999
set linesize 120
prompt
prompt SCOTT sessions
col machine format a25
col username format a15
col username format a8
col program format a25
select
username,
program,
status,
last_call_et seconds_since_active,
to_char(logon_time, 'dd-MON-yyyy HH24:MI:SS') "Logon"
from v$session
where username = 'SCOTT'
/
The result is as follows which clearly shows that the PROGRAM field has been populated by our mid tier data source:
2 comments:
Pas,
I am trying to the same thing using JNDI but not achieving any success.
It works when you try using DriverManager.getConnection, but fails when you want to use DataSource ds = (DataSource)ctx.lookup(datasourceName);
Here is my little code, which is not giving me any value for v$session.program.
-----------------------------------
java.util.Properties params = new java.util.Properties();
params.put("v$session.program", "MyApp");
Context ctx = new InitialContext(params);
DataSource ds = (DataSource)ctx.lookup(datasourceName);
-----------------------------------
Any insights ??
Thanks,
Jayesh
Jayesh,
Those properties need to be set at the data source level in the data-sources.xml and this is only supported in OAS 10.1.3.x. The Context is used to provide authentication details for example prior to obtaining a resource from the server and cannot be used to set JDBC properties as you have tried, so it's ignored when you try that.
If your using OAS 10.1.2 this solution won't help but if your using OAS 10.1.3.x it will work as I have shown.
Thanks
Pas
Post a Comment