Hi,
I want to automatically submit two commands on each newly created connection to my DB but I cannot work out how to do this with Hibernate. I was hoping there would be an interceptor or event that would allow me access to the new connection just after it has been created, but I've been unable to find such a thing. Extending the dialect class is also a possibility but it does not appear to have anything relating to connection initialisation either.

The two commands are to make Oracle 10g case-insensitive and ought to be submitted when the connection is first obtained from Oracle. They are:

alter session set nls_comp=linguistic
alter session set nls_sort=binary_ci

Pointers to the best (transparent) approach using Hibernate would be most appreciated.

Cheers,
Paul

P.S. Using a database trigger like the following would seem like a good idea, however the Oracle Thin JDBC driver sets nls_language to your default locale on each new connection. This has the side effect of setting nls_sort back to 'binary':

create or replace trigger on_connect after logon on database
begin
execute immediate 'alter session set nls_comp=linguistic';
execute immediate 'alter session set nls_sort=binary_ci';
end;

I tried this but it is not giving expected results. Its working fine from plsql prompt.

Subclass the org.hibernate.connection.DatasourceConnectionProvider, override the getConnection() method, and place your commands there:



Then declare your class as the connection provider in hibernate.properties (or whatever you use to configure Hibernate):

hibernate.connection.provider_class=your.class.goes.here

HTH,

Alex