How to configure Oracle NLS for case insensitive searches

Applies to: Denodo 7.0 , Denodo 6.0 , Denodo 5.5 , Denodo 5.0
Last modified on: 28 Feb 2018
Tags: Case sensitivity Oracle JDBC data sources

Download original document

You can translate the document:

Content

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:

BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = BINARY_AI NLS_COMP = LINGUISTIC';
END;

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.

References

Linguistic Sorting and String Searching

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training