Reputation: 6190
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.
Does the oci jdbc driver execute these queries to synchronize the NLS settings on the client to that with the database server?
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.
Upvotes: 4
Views: 5167
Reputation: 42474
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.
Upvotes: 0
Reputation: 3544
I have been wrestling with the exact same problem after introducing a trigger on logon for setting these values.
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.
Upvotes: 3