Getting application client's id to Oracle through iBatis

["Jeynes, Paul [CIB-IT]" <pa...@citigroup.com>]

Hi,

I'm trying to pass a client's login identifier through to my Oracle database via iBatis.  I'm not having much success at present and wondered if others have implemented a working solution?  My datasource connects to the Oracle db as a standard single Oracle user but users logon to the web app with their own specific login identifier.  

I want to use database auditing facilities to audit all actions as the specific user, not just the single Oracle user the datasource connects as.  This information is captured in v$session view within Oracle (holds details on the a single session/connection).

As a test, I'm trying to pass these values through on the iBatis connection via a simple method that returns some data.

    public List<Trade> getTrades() throws DataAccessException {
        try { //trying to pass the details to Oracle
            OraUtils.registerContextValues(getSqlMapClientTemplate().getDataSource().getConnection().getMetaData().getConnection(),"This is a Test", "Paul", "getTrades");
        } catch (SQLException e) {
          //Do something here
        }
        //execute the actual query
        return getSqlMapClientTemplate().queryForList("getTrades", null);
    }

The "regsiter values" method is:

    public static void registerContextValues(Connection conn, String action, 
                                             String client, 
                                             String module) throws SQLException {
        OracleConnection c = (OracleConnection) conn;
        String[] metrics = new String[c.END_TO_END_STATE_INDEX_MAX];
        metrics[c.END_TO_END_MODULE_INDEX] = module;
        metrics[c.END_TO_END_ACTION_INDEX] = action;
        metrics[c.END_TO_END_CLIENTID_INDEX] = client;
        metrics[c.END_TO_END_ECID_INDEX] = "ECID?";
        c.setEndToEndMetrics(metrics, (short)0);
    }

Currently, I'm not getting the client details registered in the database.  The theory is that "Paul" would be registered as the CLIENT_IDENTIFIER against the connection used by iBatis to connect to the database - this value would then be accessible via the v$session Oracle view (along with the other details I'm sending).

Apologies if this is more an Oracle question than iBatis.  I ask in case others have solved this in an iBatis/Oracle environment, maybe in a different way.  Basically, I'd like to somehow get "Paul" into the v$session view as CLIENT_IDENTIFIER.

Thanks!