Search code examples
jdbcoracle-call-interfacenls

OCI JDBC Driver and NLS Settings

We have created a "after logon" trigger to alter the session in order to set the two NLS settings - NLS_SORT and NLS_COMP. NLS_SORT needs to be set to BINARY_CI and NLS_COMP needs to be set to LINGUISTIC. By querying the V$NLS_PARAMETERS view after establishing the connection to verify the values I see that NLS_COMP parameter gets updated but NLS_SORT doesn't.

After researching more I realized that the OCI JDBC driver executes the below query after the connection is established

        ALTER SESSION SET NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA'

Since NLS_SORT derives its values from NLS_LANGUAGE, NLS_SORT parameter value resets to BINARY instead of BINARY_CI. The NLS_LANGUAGE setting is taken from windows registry.

  1. Does the oci jdbc driver execute these queries to synchronize the NLS settings on the client to that with the database server?

  2. Can the jdbc oci driver be configured to avoid executing the alter session set NLS_LANGUAGE query?

The reason I ask the second question is that it doesn't make sense for application server (e.g glassfish, tomcat, jboss) machine (on which the OCI client is installed) settings to override the database server settings.

Solution

  • I have been wrestling with the exact same problem after introducing a trigger on logon for setting these values.

    1. "The purpose of the NLS_LANGUAGE and NLS_TERRITORY settings are to let the database know locale information. These are derived from the NLS_LANG setting on the client (but can also be changed). These settings allow the database to send data back to the client in the expected format and language." - taken from here
    2. No, it can't be configured to avoid that execution, though it might be a future feature. As it is, it seems the only solution is to have your application always change these parameters after logging in.

    It might be of help to know that if you're using the thin connector rather than the OCI-driver and you set an on logon trigger, these values will be set properly after logon. The problem here is with the OCI-driver that will set up these values according to NLS_LANG only after the logon trigger has been executed.