CODEWITHSUNDEEP
jdbcoracle-call-interfacenls
Andy Dufresne
Andy Dufresne

Reputation: 6190

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.

Upvotes: 4

Views: 5167

Answers (2)

Piotr Dobrogost
Piotr Dobrogost

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

Stefan Sveidqvist
Stefan Sveidqvist

Reputation: 3544

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.

Upvotes: 3

Related Questions