3

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.

Andy Dufresne
  • 6,022
  • 7
  • 63
  • 113

2 Answers2

3

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.

Community
  • 1
  • 1
Stefan Thyberg
  • 3,445
  • 3
  • 23
  • 29
  • 1
    Yes. There is an oracle enhancement request filed to fix this for the OCI driver. After the fix the language and the territory settings would be passed on to the server at the time of connect instead of passing them after the getConnection() call as it is done now. This fix will be available in version 12. Hence the only solution now is to execute the alter session queries. – Andy Dufresne Nov 16 '11 at 04:45
  • @AndyDufresne Could you please provide reference to the *enhancement request filed to fix this for the OCI driver* you mentioned? – Piotr Dobrogost Feb 16 '17 at 16:14
  • @Piotr Dobrogost - You can track id – 39670004 which was planned to be fixed in 12.1 earlier. I do not know the current status right now. – Andy Dufresne Feb 17 '17 at 10:22
  • 1
    @AndyDufresne The correct bug number is 3967**00**4 not 39670004. – Piotr Dobrogost Feb 17 '17 at 15:35
0

According to chapter 19 Globalization Support from Home / Database / Oracle Database Online Documentation 12c Release 1 (12.1) / Application Development / Database JDBC Developer's Guide:

Starting from Oracle Database 10g, the NLS_LANG variable is no longer part of the JDBC globalization mechanism. The JDBC driver does not check NLS environment. So, setting it has no effect.

ALTER SESSION statement which you observed probably comes from the following statement from Globalization Support for JDBC Drivers section of chapter 9 Java Programming in a Global Environment from Oracle9i Database Globalization Support Guide (Release 2 (9.2)):

At database connection time, the JDBC Class Library sets the server NLS_LANGUAGE and NLS_TERRITORY parameters to correspond to the locale of the Java VM that runs the JDBC driver. This operation is performed on the JDBC OCI and JDBC thin drivers only, and ensures that the server and the Java client communicate in the same language.

Piotr Dobrogost
  • 41,292
  • 40
  • 236
  • 366