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

[{'answer_id': 53449, 'author': 'Tony BenBrahim', 'author_id': 80075, 'author_profile': 'https://Stackoverflow.com/users/80075', 'pm_score': 2, 'selected': True, 'text': '<p>yes, you can write a wrapper class around your connection pool, and a wraper around the connection\nso lets say you have:</p>\n\n<pre>\nOracleConnection conn=connectionPool.getConnection("java:scott@mydb");\n</pre>\n\n<p>Change it to:</p>\n\n<pre>\npublic class LoggingConnectionPool extends ConnectionPool{\n    public OracleConnection getConnection(String datasourceName, String module, String action){\n        OracleConnection conn=getConnection(datasourceName);\n        CallableStatement call=conn.preparedCall("begin dbms_application_info.setModule(module_name => ?, action_name => ?); end;");\n        try{\n            call.setString(1,module);\n            call.setString(2,action);\n            call.execute();\n        finally{\n            call.close();\n        }\n        return new WrappedOracleConnection(conn);\n    }\n</pre>\n\n<p>Note the use of WrappedOracleConnection above. You need this because you need to trap the close call</p>\n\n<pre>\npublic class WrappedOracleConnection extends OracleConnection{\n    public void close(){\n        CallableStatement call=this.preparedCall("begin dbms_application_info.setModule(module_name => ?, action_name => ?); end;");\n        try{\n            call.setNull(1,Types.VARCHAR);\n            call.setNull(2,Types.VARCHAR);\n            call.execute();\n        finally{\n            call.close();\n        }\n    }\n\n    // and you need to implement every other method\n    //for example\n    public CallableStatement prepareCall(String command){\n        return super.prepareCall(command);\n    }\n    ...\n}\n</pre>\n\n<p>Hope this helps, I do something similar on a development server to catch connections that are not closed (not returned to the pool).</p>\n'}, {'answer_id': 378045, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': '<p>If you are using JBoss, you can use a "valid-connection-checker".\nThis class is normaly used to check the validity of the Connection.\nBut, as it will be invoked every time the Connection pool gives the user a Connection, you can use it to set the DBMS_ APPLICATION _INFO.</p>\n\n<p>You declare such a class in the oracle-ds.xml like this:</p>\n\n<pre><code>&lt;local-tx-datasource&gt;\n    &lt;jndi-name&gt;jdbc/myDS&lt;/jndi-name&gt;\n    &lt;connection-url&gt;jdbc:oracle:thin:@10.10.1.15:1521:SID&lt;/connection-url&gt;\n    &lt;driver-class&gt;oracle.jdbc.driver.OracleDriver&lt;/driver-class&gt;\n    &lt;security-domain&gt;MyEncryptDBPassword&lt;/security-domain&gt;\n    &lt;valid-connection-checker-class-name&gt;test.MyValidConn&lt;/valid-connection-checker-class-name&gt;\n    &lt;metadata&gt;\n        &lt;type-mapping&gt;Oracle9i&lt;/type-mapping&gt;\n    &lt;/metadata&gt;\n&lt;/local-tx-datasource&gt;\n</code></pre>\n\n<p>Your class must implement the org.jboss.resource.adapter.jdbc.ValidConnectionChecker interface.\nIf you use Maven, you can include this interface with the following dependency:</p>\n\n<pre><code>&lt;dependency&gt;\n    &lt;groupId&gt;jboss&lt;/groupId&gt;\n    &lt;artifactId&gt;jboss-common-jdbc-wrapper&lt;/artifactId&gt;\n    &lt;version&gt;3.2.3&lt;/version&gt;\n    &lt;scope&gt;provided&lt;/scope&gt;\n&lt;/dependency&gt;\n</code></pre>\n\n<p>This interface has only one method: isValidConnection.\nI copy my implementation:</p>\n\n<pre><code>public SQLException isValidConnection(Connection arg0) {\n    CallableStatement statement;\n    try {\n        statement = arg0.prepareCall("call dbms_application_info.set_client_info(\'"+getInfos()+"\')");\n        statement.execute();\n    } catch (SQLException e) {\n        // TODO Auto-generated catch block\n        e.printStackTrace();\n    }\n    return null;\n}\n</code></pre>\n\n<p>Hope it helps !</p>\n\n<p>Benoît</p>\n'}, {'answer_id': 9675451, 'author': 'Nicholas', 'author_id': 43786, 'author_profile': 'https://Stackoverflow.com/users/43786', 'pm_score': 0, 'selected': False, 'text': '<p>In your <em>-ds.xml</em>, you can set a connection property called <strong>v$session.program</strong> and the value of that property will populate the <strong>PROGRAM</strong> column of each session in the <strong>V$SESSION</strong> view created for connections originating from your connection pool. I usually set it to the <em>jboss.server.name</em> property.</p>\n\n<p>See <a href="http://www.magpiebrain.com/2006/05/09/specifying-a-program-name-in-oracle-jdbc-connections/" rel="nofollow">here</a> for an example.</p>\n'}]
['https://Stackoverflow.com/questions/53379', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3839/']