Best way to set NLS_SORT for Oracle?

[xerces8]

Hi!

Using Spring JDBC with Oracle 11g, what is the best way to set the NLS_SORT variable?
We will be using a connection pool.

- Environment variable on the client seems a bit too out of control.
- ALTER SESSION - when to call it? will the use of connection pool interfere with this?

Regards,
David

[cjenkins]

We've found the easiest way is to set it using a logon trigger (on our application user account).

create or replace
trigger set_nls_onlogon
AFTER LOGON ON SCHEMA
DECLARE
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=''BINARY_CI''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=''LINGUISTIC''';
END set_nls_logon;

[marcelstoer]

Using Spring JDBC with Oracle 11g, what is the best way to set the NLS_SORT variable?

"The best" is obviously highly subjective.
The JDBC driver derives these settings for the session from java.util.Locale. So, you could do

Locale.setDefault(Locale.<your locale here>);

during start-up of your app somehow. Personally, I prefer setting

-Duser.language=<lang> -Duser.country=<2-char-ISO-country-code>

as JVM arguments.