CODEWITHSUNDEEP
springoracle-databasemybatis
Jose Carlos
Jose Carlos

Reputation: 11

How to set the client identifier in Oracle using Spring/myBatis

I'd like to know how to set the CLIENT_IDENTIFIER parameter in Oracle in order to have the application user available during invocations to the database. I'm using the Spring framework with myIbatis.

I'll be very grateful if you can give me precise instructions on how to do that because I am not an expert using these technologies.

Upvotes: 1

Views: 6192

Answers (3)

Sheekat
Sheekat

Reputation: 21

You can set using the Spring AOP. Write an aspect that will invoke whenever getConnection method called.

@Component
@Aspect
public class ClientIdentifierConnectionPreparer implements ConnectionPreparer {

    private String prepSql = "{ call DBMS_SESSION.SET_IDENTIFIER(?) }";
    @Autowired
    private UserService userService;

    @AfterReturning(value="execution(java.sql.Connection javax.sql.DataSource.getConnection(..))" returning="connection")
    public Connection prepare(Connection connection) {
        CallableStatement cs = connection.prepareCall(prepSql);
        cs.setString(1,userService.getUserId());
        cs.execute();
        cs.close();
        return connection;
    }
}

Upvotes: 2

Marmite Bomber
Marmite Bomber

Reputation: 21075

Check Oracle documentation for setEndToEndMetrics for older Versions; for 12c setClientInfo.

In either case you need a real connection, not the proxy.

The advantage of using this approach compared to a call of DBMS_APPLICATION_INFO is that it needs no roundtrip to the database. The information is transferred and set with the next JDBC call.

UPDATE

A simple example for iBatis follows.

1) You must unwind the connection to get the real Oracle connection

 Connection con = unwindConnection(sqlMapClient.getCurrentConnection());

2) define the E2E identifiers - as you correct stated after requesting the connection from pool or event before each action

  String[] metrics = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
 metrics[OracleConnection.END_TO_END_ACTION_INDEX] = 'myAction3';
 metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = 'myClient3';
 metrics[OracleConnection.END_TO_END_MODULE_INDEX] = 'myModule3';

 con.setEndToEndMetrics(metrics, (short) 0);

3) after the next JDBC roundtrip to DB the identifier are set

 select    ACTION, CLIENT_IDENTIFIER, MODULE 
 from v$session where ....

 myAction3   myClient3   myModule3

Upvotes: 2

Matthew McPeak
Matthew McPeak

Reputation: 17934

For CLIENT_INFO

BEGIN DBMS_APPLICATION_INFO.SET_CLIENT_INFO('whatever'); END;

For CLIENT_IDENTIFIER

BEGIN DBMS_SESSION.SET_IDENTIFIER('whatever'); END;

Upvotes: 3

Related Questions