using DBMS_APPLICATION_INFO with Jboss

Tags: jboss oracle java
Question!

Does anyone have examples of how to use DBMS_APPLICATION_INFO package with JBOSS?

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.

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.

Has anyone done this before?



Answers

In your -ds.xml, you can set a connection property called v$session.program and the value of that property will populate the PROGRAM column of each session in the V$SESSION view created for connections originating from your connection pool. I usually set it to the jboss.server.name property.

See here for an example.

By : Nicholas


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:[email protected]");

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).



This video can help you solving your question :)
By: admin