Chapter 15 Database Globalization

Globaliztion Capabilities
Character Sets :-
US7ASCII Single-byte seven-bit
WE8ISO8859P15 Single-byte eight-bit
AL16UTF16 Unicode fixed-width two-byte character set
AL32UTF8 Unicode varying_width character set
UTF8 Unicode varying_width character set ranging from one to four bytes (standard on Unix systems)

Language Support
select * from v$nls_valid_values where parameter='LANGUAGE';
NLS_LANGUAGE default AMERICAN
NLS_DATE_LANGUAGE default AMERICAN
NLS_SORT default BINARY
select * from v$nls_valid_values where parameter='SORT';

Territory Support
select * from v$nls_valid_values where parameter='TERRITORY';
NLS_TERRITORY
NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY
NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT
NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT
NLS_NUMERIC_CHARACTERS: .,

Other NLS Settings
NLS_CALENDAR
NLS_COMP: BINARY / ANSI
NLS_LENGTH_SEMANTICS: BYTE / CHARACTER
NLS_NCHAR_CONV_EXCP: FALSE / TRUE for error message conversion between VARCHAR2 and NVARCHAR

NLS_CHARACTERSET
NLS_NCHAR_CHARACTERSET
NLS_RDBMS_VERSION


Using Globalization Support Features

1. The database: nls_database_parameters
2. The instance: nls_instance_parameters
3. The client environment: NLS_LANG=language_territory.characterset
4. The session: V$nls_parameters, nls_session_parameters
5. The statement

Database Character Set default US7ASCII / OS default NLS setting
National Character Set + Unicode default AL16UTF16

Changing Character Sets
Database Character Set Scanner:
csscan system/password full=y tochar=utf8 (run csminst.sql script first)
Language and Character Set File Scanner: lcsscan to scan text file to guess character set

Work with Database and NLS Character Sets
Linguistic Sorting and Selection
Linguistic Sort: numeric value that reflects its correct position in the sequence appropriate to the language in use.

Monolingual Sort: FRENCH
first pass based on the "major" value of each character (Uppercase with no accents)
second pass using the "minor" value (Case and diacritic sensitive)

Multilingual Sort: FRENCH_M

Case and diacritic insensitive Sort:
NLS_SORT=GENERIC_BASELETTER
NLS_COMP=ANSI

Accent insensitive Sort: Suffixed with _AI
Case insensitive Sort: Suffixed with _CI

Locale Builder: lbuilder

Using Time Zones
Timestamp with time zone: Not normalize to the database time zone. time zone indicator
Timestamp with local time zone: Normalize to the database time zone. converted to client time zone on retrieval.
Timestamp: Normalize to the database time zone. Display unchanged.
Date: Normalize to the database time zone. Display unchanged.

Client time zone default to that of the client operating system or it can be set with environment variable ORA_STDZ

ALTER DATABASE SET TIME_ZONE=
ALTER SESSION SET TIME_ZONE=
V$TIMEZONE_NAMES