Tips7_Managing Globalization in Oracle Databases

■Oracle 10g ships with more than 250 character sets.

SQL> select distinct parameter from v$nls_valid_values;

PARAMETER

---------------------------------------------------------

CHARACTERSET

SORT

TERRITORY

LANGUAGE

SQL>alter session set nls_lang='AMERICAN'

■■

・Language Support

The language used will determine the language for error messages and also set defaults for date language and sort orders.

NLS_LANGUAGE

NLS_DATE_LANGUAGE

NLS_SORT

・Territory Support

The territory selection sets defaults for day and week numbering, credit and debit symbols, date formats, decimal and group numeric separators, and currency symbols.

NLS_TERRITORY

NLS_CURRENCY

NLS_TIMESTAMP_FORMAT

NLS_TIMESTAMP_TZ_FORMAT

NLS_DUAL_CURRENCY

NLS_ISO_CURRENCY

NLS_DATE_FORMAT

NLS_NUMERIC_CHARACTERS

・Other NLS Settings

NLS_CALENDAR

NLS_COMP

NLS_LENGTH_SEMANTICS

NLS_NCHAR_CONV_EXCP

■Using Globalization Support Features

the database character set and the National Character Set

Unicode：

• AL16UTF16　 is a fixed-width, two-byte character set

• UTF8　 is a variablewidth character set

• WE8ISO8859P15 Eight-bit single-byte

• US7ASCII   Seven-bit single-byte

■Changing Character Sets

There are two tools provided to assist with deciding on character set change: 

①the Database Character Set Scanner （log on to the database and make a pass through the datafiles, generating a report of possible problems）

②the Language and Character Set File Scanner.

These are independently executable utilities, csscan and lcsscan on Unix, csscan.exe and lcsscan.exe on Windows

eg:

1)

csscan system/manager full=y tochar=utf8

・You must run the csminst.sql script to prepare the database for running the character set scanner.

2)

ALTER DATABASE CHARACTER SET...

ALTER DATABASE NATIONAL CHARACTER SET....

※The only limitation with this command is that the target character set must be a superset of the original character set, but that does not guarantee that there will be no corruptions.

■■■five levels:

• The database

• The instance

• The client environment

• The session

• The statement

■Globalization Within the Database

SQL> select * from nls_database_parameters;

■Globalization at the Instance Level

SQL> select * from nls_instance_parameters;

■Client-Side Environment Settings

SQL> show parameter nls_lang

win:

C:\>set NLS_LANG=Hungarian

C:\>set NLS_DATE_FORMAT=Day dd Month yyyy

Unix:

$ export NLS_LANG=Hungarian

$ export NLS_DATE_FORMAT=Day dd Month yyyy

The conversion between server-side and client-side Globalization settings is done by Oracle Net.

■Session-Level Globalization Settings

SQL> select * from V$NLS_PARAMETERS

NLS_SESSION_PARAMETERS

SQL> alter session set nls_date_format='dd.mm.yyyy';

Session altered.

SQL> execute dbms_session.set_nls('nls_date_format','''dd.mm.yyyy''');

PL/SQL procedure successfully completed.

■Using Time Zones

V$TIMEZONE_NAME

ALTER SESSION SET TIME_ZONE=....

ALTER DATABASE SET TIME_ZONE=....

■The Locale Builder

The Locale Builder is a graphical tool that can create a customized Globalization environment, by generating definitions for languages, territories, character sets, and linguistic sorting.

Unix:

$ORACLE_HOME/nls/lbuilder/lbuilder

Win:

%ORACLE_HOME%\nls\lbuilder\lbuilder.bat

■

V$NLS_VALID_VALUES will show you the full range of supported languages, as well as all other globalization options.