This document describes how to configure Oracle NLS to support case insensitive searches.
Oracle databases are by default configured as case sensitive. Version 10gR2 added a new feature to its NLS (National Language Support) that allows to do case-insensitive sorts and comparisons altering the session settings.
This can be done changing the value of two NLS parameters, NLS_SORT and NLS_COMP.
The NLS_SORT parameter governs the collation sequence for ordering and the various comparison operators, including ‘=’ and ‘LIKE’.
You can specify a binary, case-insensitive, sort by altering the session, for instance:
alter session set nls_sort=BINARY_CI;
This will mean that every query performed in that session will perform case-insensitive searches.
Further information about linguistic sorting and string searching can be found here. For instance, you can specify a different language, or you can use BINARY_AI to configure an accent-insensitive search.
The NLS_COMP parameter decides the exact operators and query clauses that are going to follow the NLS_SORT parameter.
The default value of NLS_COMP is BINARY, this means that queries are not going to follow the NLS_SORT configuration. In order for Oracle to pay attention to the value of NLS_SORT, we must change the value of NLS_COMP to LINGUISTIC:
alter session set nls_comp=LINGUISTIC;
Since these parameters can be modified at session level we can configure this for any Oracle JDBC data source in Virtual DataPort. To do this we are going to use the Ping query of the data source configuration.
The Ping query is used to test the connections obtained from the pool before executing a query. So, as this Ping query is always executed before executing the actual query, the session can be configured to allow case-insensitive searches in advance to the query that is being issued.
As an example the following Ping query can be used:
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_AI NLS_COMP = LINGUISTIC';
as shown in the next figure:
Note that the ‘Test connections’ checkbox needs to be checked so the Ping query is always executed. This configuration can be also applied when configuring an Oracle database as the cache.
As an additional recommendation on the use of ping queries. Take into account that this query is going to be executed every time the VDP server checks if a connection from the pool is valid, so it is very important to use a query consuming few resources in the database.