Transcript Database Administration

Week 7
Lecture 2
Globalization Support in the
Database
Learning Objectives

Examine how globalization support is
implemented in the database

Use globalization parameters and
variables

View globalization support information in
data dictionary views
National Language Support
vs. Globalization Support

National Language Support (NLS) makes it
possible for tables to store data from multiple
languages
 Globalization support allows you to design a
database that supports users from many
nationalities by converting data to each user’s
native language
 NLS is a subset of Globalization support
Introduction to Globalization
Support




Oracle now has support for the Euro
ALTER TABLE MODIFY can now convert a CHAR
column to an NCHAR column
New Oracle-supplied packages support conversion of
BFILE data into NLS character data when loading a
NCLOB column
Oracle’s database structure now supports storing data
in Unicode
Language-Dependent
Behavior in the Database




Sorting, date formatting, currency formatting, and
similar things must be altered
Behind the scenes, the database stores data using
bytes, as it does with English
Even though the database stores data in any
language, it requires that you store SQL and PL/SQL
in English
The database can convert data to and from different
languages, so that each client sees the appropriate
language
Language-Dependent
Behavior in the Database
An example database supporting 3 clients in different languages:
Database
Spanish Client
Database Translations
French Client
English Client
Using NLS Parameters and
Variables





An Oracle9i database has a base language called its database character
set
A second language, called the national character set, can be used to
store data in columns that are defined as one of the three NLS data types
Both character sets are designated when you create the database by
specifying the CHARACTER SET and NLS CHARACTER SET
After the database is created, you cannot change these two parameters
Use initialization parameters to fine-tune the database’s behavior
regarding the NLS character set you have chosen
Example Character Sets

Three variations of Japanese character sets:




Simplified Chinese:


F7DEC
Latin/Arabic:


D7DEC
French:


US7ASCII
German:


ZHT16CCDC
American:


ZHS16CGB231280
Traditional Chinese


JA16EUC
JA16EUCTILDE
JA16EUCYEN
AR8APTEC715
Latin/Hebrew

IW8ISO8859P8
Different Types of Character
Sets

UTF8 is a universal language character set

Using the UTF8 character set can simplify translations from one
language to another when the database has multilingual clients

US7ASCII is a single-byte character set

Many other character sets are single-byte character sets,
especially for languages similar to English, such as French, Latin,
Spanish, Italian, and many more Western European languages
Different Types of Character
Sets






UTF8 is a multibyte character set
A multibyte character set can be either fixed-length or variable-length
A fixed-length multibyte character set uses a fixed number of bytes for
each character in the character set
Calculating the length of a data string is easier with fixed-length
character sets
A variable-length multibyte character set uses a variable number of
bytes to represent one character in the character set
Variable-length character sets save space but take longer for
processes such as calculating the length of a data string
Adjusting Globalization
Initialization Parameters



Many NLS-related initialization parameters in Oracle9i
All of them affect how the database handles national language data
In addition to being modified in the init.ora file, NLS initialization
parameters can be modified at two levels:
SQL level: Changing the value of an initialization parameter within an SQL
function or an SQL statement alone. Use special NLS clauses to
accomplish this.
Session level: Changing the value of an initialization parameter in the
current session only. Use the ALTER SESSION command to do this.

The value of a parameter at the narrower level always overrides the value
of the same parameter at the broader level
Database Properties
Affecting Language

NLS_LANGUAGE:
 Affects the language used for messages, day and month names,
and sorting order, among other things
 NLS_TERRITORY:
 Affects how dates are formatted, what currency is used by default,
and the numbering format of days and weeks

NLS_CURRENCY:
 Changes the symbol used for currency (for example, “$” is the
default when NLS_TERRITORY is AMERICA)
More Database Properties
Affecting Language

NLS_DATE_FORMAT:
 Changes the default format of dates, as set by
NLS_TERRITORY. The format defines the expected format
of dates in the TO_DATE function.

NLS_DATE_LANGUAGE:
 Changes the spelling of days, months, and abbreviations for
time and date, such as AM, PM, and AD. The default for this
parameter is defined by the NLS_LANGUAGE parameter.
More Database Properties
Affecting Language


NLS_ISO_CURRENCY:
 Changes the international currency symbol set by the
NLS_TERRITORY parameter.
NLS_NUMERIC_CHARACTERS:
 Changes the symbols used as decimals and separators in
numbers. The default value is set by the NLS_TERRITORY
parameter.
 For example, in the AMERICA territory, a comma (,) is the
separator and a period (.) is the decimal point. In the FRANCE
territory, a period (.) is the separator and a comma (,) is the
decimal point.
More Database Properties
Affecting Language

NLS_SORT:
 Changes the method used to sort character data.
 Default is set by the NLS_TERRITORY parameter, so that
sorting is done according to the language of the data.
 BINARY is the default for the NLS_TERRITORY of
AMERICA; however, other values, such as JAPANESE,
reflect the name of the language.
 In the case of the English language, the order of the
alphabet is reflected by the order of the numeric values
stored for the symbol for each letter, so the sort is based on
the numerical values of the characters, hence the name
BINARY.
Using NLS Parameters in
SQL

Can add NLS parameters to the TO_CHAR,TO_DATE, and
TO_NUMBER functions to use language-dependent values
 For example, the TO_CHAR command has the following syntax:
TO_CHAR(<colname>,’<format>’,’<nls_parameters>’)

Can specify up to three parameters in the TO_CHAR function to
modify the default values set up by the NLS_TERRITORY
parameter
 You must use the appropriate placeholder in the format for each
parameter
Using NLS Parameters in
SQL



NLS_CURRENCY:
 Sets the symbol of the local currency. Placeholder is L.
NLS_NUMERIC_CHARACTERS:
 Sets the symbols for the decimal point (placeholder is D) and
group separator (placeholder is G).
NLS_ISO_CURRENCY:
 Sets the symbols for the international (ISO) currency.
Placeholder is C.
Using NLS Parameters in
SQL

You can specify the language-dependent sort order within the ORDER
BY clause, which has the following syntax:
ORDER BY
NLSSORT(<sort_column>,’NLS_SORT=<language>’);

ORDER BY clause sorts by using the results of the NLSSORT function
that changes a column’s data into an alphanumeric string that sorts
according to the language-dependent properties of whatever language
you specify
Changing NLS Parameters
in Your Session




Can also change NLS parameters so that all queries use your
session settings and no one else using the database is affected
Best way to do this is by adjusting the two primary parameters
(NLS_LANGUAGE and NLS_TERRITORY)
This is done with the ALTER SESSION command
Initialization parameters in the init.ora file should be set up to
handle the NLS requirements for typical requests and reports
Viewing NLS Parameters in
Data Dictionary Views

NLS_DATABASE_PARAMETERS


NLS_INSTANCE_PARAMETERS



Lists NLS parameter settings for the database instance. Only shows
values that have been set in the init.ora file. Parameters left to default are
shown as null values
NLS_SESSION_PARAMETERS


Lists NLS parameter settings for the database, including
NLS_CHARACTER_SET
Lists NLS parameters in force for the current session. Does not include
NLS_CHARACTER_SET
V$NLS_PARAMETERS
 Lists current values of NLS parameters
V$NLS_VALID_VALUES

Lists all valid values for these NLS parameters: CHARACTERSET,
LANGUAGE, SORT, TERRITORY
Final Points About NLS

With few exceptions, you choose the default
character set and the NLS character set at database
creation and never change them.

Other NLS parameters can be modified by changing
the initialization parameters, adjusting them in your
session, or adding them to an SQL statement.
Lecture Summary




National Language Support (NLS) allows one database to
store multiple languages of data
Globalization support includes NLS support and more
capabilities aimed at producing a multilingual database
Globalization support has improved with new features in
Oracle9i
SQL and PL/SQL must be entered in English, even if the
native language is not English
Lecture Summary





The database character set is the default character set
used for all but NLS data types
NLS data types are NCHAR, NVARCHAR2, and NCLOB
The national character set is used for NLS data types
Character sets are defined when the database is created
A character set is a map of characters to machine language
codes
Lecture Summary





UTF8 is a universal language character set capable of
supporting all languages supported by Oracle9i
Character sets are either single-byte or multibyte.
Multibyte character sets are either fixed-length or variablelength
NLS-related initialization parameters can be set in the
init.ora file, a session, or a specific SQL statement
Use the Instance Manager to examine NLS parameters and
their definitions
Lecture Summary




Add NLS parameters to the TO_CHAR,TO_DATE, and
TO_NUMBER functions to use values other than defaults
Use language-dependent sorting through the NLSSORT
function in the ORDER BY clause
Number formatting can be done with the TO_CHAR
function and includes changing the currency, decimal point,
and group separator symbols
American English and French sorting orders are different
Lecture Summary




Adjust sorting order by including NLSSORT in the ORDER
BY clause
The NLS_SESSION_PARAMETERS view lists NLS
parameters currently set for your session
Changing NLS_LANGUAGE does not translate text data
into another language
V$NLS_VALID_VALUES lists all valid values for some of
the NLS parameters