I'm working on a number of services that connect to an Oracle db using a connection pool via oracle.jdbc.pool.OracleDataSource. What I'm trying to figure out is, when the getConnection() method is called, is it possible to identify whether the connection that is established to the db is a new physical connection or indeed an recycled logical connection? I was thinking of using AOP to capture the getConnection() event and verify if a new physical connection is made, I';m just wondering if this is even possible?
It sorts of depends on what you intend to do. In Oracle, you could use the DBMS_APPLICATION_INFO package to "mark" connections. Upon retrieving a connection from the pool, you'd check the client or module info. If it was not set, you'd know the connection is new, and subsequently set the info to a specific value of yours. A small benefit of this is that it help identify the connections of your application in the database and would be independent of the connection pool. The downside is that it is be Oracle specific.
If you just need to set up a new connection somehow (eg. alter NLS settings), an on-logon trigger might do the trick as well without a need to modify your application.
Joined: Feb 03, 2012
Thanks for your reply. To give some background, that I am trying to do is to set a session variable value using DBMS_SESSION.SET_CONTEXT, but only set it if a certain module is connecting to the db. I have two client applications connecting to the DB, using the same db credentials, but separate JDBC connection configurations. What I want to do is each time one of the client applications makes a physical connection to the pool (i.e. setting up a new session) to call my DBMS_SESSION.SET_CONTEXT stored proc to set a certain session variable value for that particular application calling the db. Rather than do this every time getConnection is called, I want it to call the stored proc each time a new physical connection is made via the connection pool
subject: Identifying a new physical connection versus a logical connection in a jdbc connection pool