Oracle Database Globalization

Home»Oracle Database Globalization

Today I explored more about Database Globalization, trigger came from a defect. The defect was that a search of data on a particular column in the database was case sensitive. Actually as per the business case the search on the particular column should not be case sensitive.

Also Found that it was because the default behavior of LIKE and the other comparison operators, = etc is case-sensitive. Also found that Since 10gR2, Oracle allows to fine-tune the behavior of string comparisons by setting the NLS_COMP and NLS_SORT session parameters.

What is NLS?

NLS (National Language Support) is used to define a national date, number, currency and language settings. For example, it can be used to change the currency symbol from $ to € (Euro); the language from English to Dutch, etc.

Database Globalization: It is a feature provided every database vendor to define the following setting for the Database tables, 1)NLS_LANGUAGE
2)NLS_TERRITORY
3) NLS_CURRENCY
4)NLS_ISO_CURRENCY
5)NLS_NUMERIC_CHARACTERS
6)NLS_CALENDAR
7)NLS_DATE_FORMAT
8)NLS_DATE_LANGUAGE
9)NLS_SORT
10)NLS_TIME_FORMAT
11)NLS_TIMESTAMP_FORMAT
12)NLS_TIME_TZ_FORMAT
13)NLS_TIMESTAMP_TZ_FORMAT
14)NLS_DUAL_CURRENCY
15)NLS_COMP
16)NLS_LENGTH_SEMANTICS
17)NLS_NCHAR_CONV_EXCP 2

Default Globalization parameters:

To know the default globalization parameter of your oracle db you are working on, fire the following query, SELECT * FROM NLS_SESSION_PARAMETERS

More about parameters

‘NLS_COMP', 'NLS_SORT': To know the default setting of the parameters you would be requiring to fire the following query,

SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

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. This will mean that every query performed in that session will perform case-insensitive parameters.

Alter session set nls_sort=BINARY_CI The default value of NLS_COMP is BINARY; but, LINGUISTIC specifies that Oracle should pay attention to the value of NLS_SORT:

Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

So, once again, you need to alter the session alter session set nls_comp=LINGUISTIC Once this is done, you would be able to do a case-insensitive search on the oracle database table.

Posted by: Jinali Patel. in Computer | Date: 30/11/2015

Share this article

Other articles

Back to article listing

Find a Great Teacher

Tell us your learning needs in detail and get immediate response from qualified tutors

Terms & conditions agreed
Ask a Question
Top