<p>Does anyone have examples of how to use <a href="http://www.oracle-base.com/articles/8i/DBMS_APPLICATION_INFO.php" rel="nofollow noreferrer">DBMS_APPLICATION_INFO</a> package with JBOSS? </p>

<p>We have a various applications which run within JBOSS and share db pools. I would like, at the start of each session these applications to identify themselves to the database using DBMS_APPLICATION_INFO so I can more easily track which sections of the application is causing database issues.</p>

<p>I'm not too familiar with session life cycles in JBOSS, but at the end of the day, what needs to happen is at the start and end of a transaction, this package needs to be called.</p>

<p>Has anyone done this before?</p>

yes, you can write a wrapper class around your connection pool, and a wraper around the connection
so lets say you have:
```
OracleConnection conn=connectionPool.getConnection("java:scott@mydb");
```
Change it to:
```
public class LoggingConnectionPool extends ConnectionPool{
    public OracleConnection getConnection(String datasourceName, String module, String action){
        OracleConnection conn=getConnection(datasourceName);
        CallableStatement call=conn.preparedCall("begin dbms_application_info.setModule(module_name => ?, action_name => ?); end;");
        try{
            call.setString(1,module);
            call.setString(2,action);
            call.execute();
        finally{
            call.close();
        }
        return new WrappedOracleConnection(conn);
    }
```
Note the use of WrappedOracleConnection above. You need this because you need to trap the close call
```
public class WrappedOracleConnection extends OracleConnection{
    public void close(){
        CallableStatement call=this.preparedCall("begin dbms_application_info.setModule(module_name => ?, action_name => ?); end;");
        try{
            call.setNull(1,Types.VARCHAR);
            call.setNull(2,Types.VARCHAR);
            call.execute();
        finally{
            call.close();
        }
    }

    // and you need to implement every other method
    //for example
    public CallableStatement prepareCall(String command){
        return super.prepareCall(command);
    }
    ...
}
```
Hope this helps, I do something similar on a development server to catch connections that are not closed (not returned to the pool).