Oracle Case Insensitivity and JDBC Drivers

Natively Oracle is case sensitive which means if you executed a query for e.g.

select emp_id, emp_salary from employee where emp_name = 'amit';

it would search for the string ‘amit’ in lowercase. We can switch this to make the search behave case insensitively by setting the NLS_COMP and NLS_SORT parameters from Oracle’s globalization support which was previously called National Language Support (NLS).

Oracle keeps three levels of NLS parameters within the database that are set and apply to the database, instance, and session levels. The instance and session levels can be easily manipulated, but a change to the database parameter settings end up with a rebuild of the database. The database view impacts the settings at the instance and session level as the defaults if not explicitly set at the lower levels.

NLS_COMP specifies the collation behavior of the database session. Values – BINARY, LINGUISTIC and ANSI. Default value – BINARY.

NLS_SORT specifies the collating sequence for ORDER BY queries. Values – BINARY and named linguistic sort. This parameter derives its value from NLS_LANGUAGE (which internally derives its value from NLS_LANG).

To make oracle case insensitive, NLS_COMP should be set to LINGUISTIC and NLS_SORT should be BINARY_CI.

Table 3-1 shows the precedence order of the different methods of setting NLS parameters. Higher priority settings override lower priority settings. For example, a default value has the lowest priority and can be overridden by any other method.

Priority	Method
1 (highest)	Explicitly set in SQL functions
2	Set by an ALTER SESSION statement
3	Set as an environment variable
4	Specified in the initialization parameter file
5	Default

When the application connects to a database, a session is created on the server. The new session initializes its NLS environment from NLS instance parameters specified in the initialization parameter file. These settings can be subsequently changed by an ALTER SESSION statement. The statement changes only the session NLS environment. It does not change the local client NLS environment. The session NLS settings are used to process SQL and PL/SQL statements that are executed on the server.

Immediately after the connection has been established, if the NLS_LANG environment setting is defined on the client side, then an implicit ALTER SESSION statement synchronizes the client and session NLS environments.

Oracle JDBC Drivers

In order to override the default values we would have to execute the alter session queries whenever a new session is created on the database. The ideal approach would be to apply a “after logon” trigger which gets executed whenever a new session is created. This approach has a problem if OCI jdbc driver is used.

There is an inconsistency between the OCI and thin JDBC drivers in ojdbc6.jar (implementation version – 11.2.0.1.0). The thin driver is currently optimized to pass the NLS_LANG and NLS_TERRITORY parameter values while the connect call is made to the database server but the oci jdbc driver isn’t. The oci jdbc driver executes an alter session query to set the NLS_LANG and NLS_TERRITORY parameter values after the database connection is established. Since the NLS_SORT parameter derives its value from NLS_LANGUAGE its value gets reseted and the value set by the “after log on” trigger is lost. An oracle bug (id – 39670004) is used to track this enhancement request for oci jdbc driver. It is planned to be fixed in version 12.1.

So if you are using an oci jdbc driver, the only solution is to execute the two alter session queries from java whenever a physical connection is established. If you are using a connection pool (which ideally should be the case) it would be tricky to make sure that these queries get executed only when an physical connection is created instead on every getConnection() request. Oracle UCP’s (Universal Connection Pool) labelling connection feature comes to the rescue in case you use this connection pool.

More details on the OCI behavior here.

Advertisement