Oracle Globalization

22 06 2011

Globalization Parameters:

Globalization used to be called National Language Support, or NLS. That is why you see the NLS acronym used in views, parameters, environment variables etc.

The view V$NLS_VALID_VALUES provides the range of values for LANGUAGE, TERRITORY, CHARACTERSET and SORT parameters.

NLS_LANGUAGE – determines language for error messages, sets defaults for date language and sort orders. NLS_TERRITORY – sets defaults for day and week numbering, credit/debit symbols, date formats, seperators and currency symbols. NLS_SORT – determines sort order / collation. Default sort order is BINARY, can be changed to be more appropriate to the language used.

To change a (session) parameter: ALTER SESSION set parameter_name=’VALUE’; ALTER SESSION SET NLS_LANGUAGE=’GERMAN’;

Globalization parameters can be set at any and all of five levels:

Priority Level Method
1(Highest) The statement Explicitly set in SQL functions
2 The session Set by an ALTER SESSION statement
3 The client environment Set as an environment variable
4 The instance Specified in the initialization parameter file
5 The database Default (On creation)

On server side, instance settings take precedence over database settings. All server side settings can be overriden on the client side.

The view NLS_DATABASE_PARAMETERS provides the parameter settings at the database (creation) level. The view NLS_INSTANCE_PARAMETERS provides the parameter settings at the instance level. V$NLS_PARAMETERS & NLS_SESSION_PARAMETERS provide the parameter setttings for your session.

Client-Side Environment Settings:

Oracle user process can pick up globalization parameters by checking the NLS_LANG environment variable. This is specified as LANGUAGE_TERRITORY.CHARACTERSET eg. NLS_LANG=FRENCH_CANADA.WEISO8859P1 The user process will issue a series of ALTER SESSION statements to implement the NLS_LANG settings. Conversion between server-side and client-side globalization settings is done by Oracle Net.

Statement Globalization Settings:

You can use NLS parameters in SQL functions to override all other settings eg. SELECT * FROM test ORDER BY NLSSORT(name, ‘NLS_SORT = BINARY_CI’);

Character Sets:

Oracle products ship with their own character sets, independent of the host operating system. Oracle’s default character sets are seven-bit ASCII or seven-bit EBCDIC, dependent on the platform. If you used “Database Creation Assistant” it will pick a default from the OS.

The naming convention for Oracle Database Character Sets is: <region><number of bits used to represent a character><standard character set name>[S|C] [S|C] indicates character sets that can be used only on the server or only on client. eg: WE8ISO8859P1 means “Western European 8-bit ISO 8859 Part 1”

Characters sets are chosen at DB creation time. Character sets can be changed (no guarantee of data preservation) since 9i.

The database character set (NLS_CHARACTERSET) is used to store all the data in columns of type VARCHAR2, CLOB, CHAR, and LONG. This can be any character set as long as it has either US7ASCII or EBCDIC as a subset. The supported Unicode options for NLS_CHARACTERSET are UTF8 or AL32UTF8. The National Character Set (NLS_NCHAR_CHARACTERSET) is used is used for NVARCHAR2, NCLOB and NCHAR. From 9i this can only be Unicode, either AL16UTF16 or UTF8.

There are two tools that assist with changing character sets: Database Character Set Scanner, csscan.exe, will check datafiles and generate a report of possible conversion problems. Language and Character Set File Scanner, lcsscan.exe, will identify the language and character set of a plain text file.

Change the character set with: ALTER DATABASE CHARACTER SET or ALTER DATABASE NATIONAL CHARACTER SET. The target character set must be a superset of the original character set.

Linguistic Sorting and Selection:

The default sort order is binary. This is usually suitable for English, but it can be incorrect for other languages. Linguistic sorting means Oracle will replace each character with a numeric value that reflects its correct position in the sequence appropriate to the language in use. Set NLS_SORT to change the sort order. Each sort order can be appended with _CI for the case-insensitive or _AI for accent-insensitive versions. To avoid specifying NLSSORT in a lot of SQL, UNION, INTERSECT, MINUS for example, you can set NLS_COMP to LINGUISTIC to specify that it should use the NLS_SORT setting for comparisons.