How to alter session NLS_COMP/SORT after a perticular query is executed

Question

My question was specific to some problem scenario where Transaction being started from Spring Container by using EntityManager then I am getting hibernate session out of EntityManager.

The overall flow:

1. In the application entityManager.createNativeQuery(searchQuery) is there to select some data.

2. I need to get the hibernate session out of entityManager then in the session I want to set nls_comp/nls_sort or I can execute the query in the entityManager itself. e.g. alter session set nls_comp=linguistic.

3. After its being done I want to disable these option once again back to its previous state. Most probably by closing the hibernate session or some mechanism. Is it the right approach?

Should I start another transaction for the 2 alter command also (I tried it gave some exception). Please let me know how to achieve it and how hibernate session are being managed inside a spring initiated transaction or I can achieve the enable/disable with entityManager itself.

Answer 1

This is how you can do it:

org.hibernate.Session session = (Session) entityManager.getDelegate();
session.doWork(new Work() {
    @Override
    public void execute(Connection connection) throws SQLException {
        PreparedStatement statement = null;
        try {                 
            statement = connection.createStatement();
            statement.addBatch("ALTER SESSION SET NLS_COMP = LINGUISTIC");
            statement.addBatch("ALTER SESSION SET NLS_SORT = SPANISH_M");
            statement.executeBatch();
        }
        finally {
            if(statement != null) {
                statement.close();
            }           
        }                                
    }
});

1. You can get the Session from entityManager using getDelegate()
2. You can execute those two statements in the current associated connection
3. The changes are for the current database session only and when the connection is closed they should be restored to defaults. For connection pooling you have to check whether they get persisted.

You don't need to call session.close(), thins will be taken care of Spring automatically when closing the current Transaction. It's rare that you have to close the session manually.

If you want to execute a logic for the current settings, it's better to write an AOP aspect to execute 1. and 2. then let the call go through only to reset it back to the previous values in the finally block. You can define your own annotation to mark all the service methods you'd like to have this logic enabled. Spring pointcuts support annotation-based selection anyway.