Announcement Announcement Module
Collapse
No announcement yet.
Best way to set NLS_SORT for Oracle? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Best way to set NLS_SORT for Oracle?

    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

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

    Code:
    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;
    Last edited by cjenkins; May 26th, 2011, 11:57 AM. Reason: added code tags

    Comment


    • #3
      Originally posted by xerces8 View Post
      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
      Code:
      Locale.setDefault(Locale.<your locale here>);
      during start-up of your app somehow. Personally, I prefer setting
      Code:
      -Duser.language=<lang> -Duser.country=<2-char-ISO-country-code>
      as JVM arguments.

      Comment

      Working...
      X