Transcript Jerry Held

Using Globalization Support
Copyright © Oracle Corporation, 2001. All rights reserved.
Objectives
After completing this lesson, you should be able to
do the following:
• Choose database character set and national
character set for a database
• Specify the language-dependent behavior using
initialization parameters, environment variables,
and the ALTER SESSION command
• Use the different types of National Language
Support (NLS) parameters
• Explain the influence on language-dependent
application behavior
• Obtain information about Globalization Support
usage
18-2
Copyright © Oracle Corporation, 2001. All rights reserved.
Globalization Support Features
• Language support
• Territory support
• Character set support
• Linguistic sorting
• Message support
• Date and time formats
• Numeric formats
• Monetary formats
Database
18-3
Copyright © Oracle Corporation, 2001. All rights reserved.
Different Types
of Encoding Schemes
Oracle supports different classes of character
encoding schemes:
•
Single-byte character sets
–
7-bit
–
8-bit
• Varying-width multibyte character sets
• Fixed-width multibyte character sets
• Unicode (AL32UFT8, AL16UTF16, UTF8)
18-5
Copyright © Oracle Corporation, 2001. All rights reserved.
Database Character Sets and
National Character Sets
18-8
Database Character Sets
National Character Sets
Defined at creation time
Defined at creation time
May not be change without
re-creation, few exceptions
May not be changed without
re-creation, few exceptions
Store data columns of type
CHAR, VARCHAR2, CLOB, LONG
Store data columns of type
NCHAR, NVARCHAR2, NCLOB
Can store varying-width
character sets
Can store Unicode using
either AL16UTF16 or UTF8
Copyright © Oracle Corporation, 2001. All rights reserved.
Guidelines for Choosing an
Oracle Database Character Set
Considerations
18-9
•
What language does the database need to
support?
•
What are interoperability concerns with system
resources and applications?
•
•
What are the performance implications?
What are the restrictions?
Copyright © Oracle Corporation, 2001. All rights reserved.
Guidelines for Choosing an
Oracle National Character Set
•
•
•
18-11
Two choices
–
AL16UTF16
–
UTF8
Is space an issue?
Is performance an issue?
Copyright © Oracle Corporation, 2001. All rights reserved.
Choosing a Unicode Solution
Unicode Database
When Should You Use a Unicode Database?
• Easy code migration for Java or PL/SQL
• Easy data migration from ASCII based data
• Evenly distributed multilingual data
• InterMedia Text Search
18-12
Copyright © Oracle Corporation, 2001. All rights reserved.
Choosing a Unicode Solution
Unicode Datatype
When should you use a Unicode datatype?
• Adding multilingual support incrementally
• Packaged applications
• Performance
Single byte database character set with a fixed
width national character set
•
18-13
Better support for UTF-16 with windows clients
Copyright © Oracle Corporation, 2001. All rights reserved.
Specifying Language-Dependent Behavior
Initialization parameter
Environment variable
ALTER SESSION
command
18-14
Copyright © Oracle Corporation, 2001. All rights reserved.
Specifying Language-Dependent Behavior
for the Server
NLS_LANGUAGE specifies:
• The language for messages
• Day and month names
• Symbols for A.D, B.C, A.M, P.M.
• The default sorting mechanism
NLS_TERRITORY specifies:
•
•
18-15
Day and week numbering
Default date format, decimal character,
group separator, and the default ISO
and local currency symbols
Copyright © Oracle Corporation, 2001. All rights reserved.
Dependent Language and Territory Default
Values
PARAMETER
NLS_LANGUAGE
NLS_DATE_LANGUAGE
AMERICAN
NLS_SORT
BINARY
NLS_TERRITORY
18-16
VALUES
AMERICAN
AMERICA
NLS_CURRENCY
$
NLS_ISO_CURRENCY
AMERICA
NLS_DATE_FORMAT
DD-MON-RR
NLS_NUMERIC_CHARACTERS
,.
Copyright © Oracle Corporation, 2001. All rights reserved.
Specifying Language-Dependent Behavior
for the Session
•
Environment variable:
NLS_LANG=French_France.UTF8
•
Additional environment variables:
– NLS_DATE_FORMAT
– NLS_DATE_LANGUAGE
– NLS_SORT
– NLS_NUMERIC_CHARACTERS
– NLS_CURRENCY
– NLS_ISO_CURRENCY
– NLS_CALENDAR
18-18
Copyright © Oracle Corporation, 2001. All rights reserved.
Character Sets
in Client-Server Architecture
NLS_LANG=<language>_<territory>.<charset>
NLS_NCHAR=<ncharset>
CREATE DATABASE ...
CHARACTER SET <charset>
NATIONAL CHARACTER SET
<ncharset>
...
18-19
Copyright © Oracle Corporation, 2001. All rights reserved.
Specifying Language-Dependent Behavior
for the Session
ALTER SESSION SET NLS_DATE_FORMAT=‘DD.MM.YYYY’;
DBMS_SESSION.SET_NLS(‘NLS_DATE_FORMAT’,
’’’DD.MM.YYYY’’’) ;
18-21
Copyright © Oracle Corporation, 2001. All rights reserved.
Linguistic Sorting
Oracle provides three types of sorting
•
Binary sorting, sorted according to the binary
values of the encoded characters
•
Monolingual sorting
•
18-22
–
Sorts in two passes
–
Based on a character's assigned major and minor
values
Multilingual sorting
–
Based on the new ISO 14651 and,
–
Unicode 3.0 Standard for multilingual collation
Copyright © Oracle Corporation, 2001. All rights reserved.
NLS Sorting
•
•
18-23
NLS_SORT specifies the type of sort for character
data
–
Is defined by the NLS_LANG environment variable
–
May be overridden at the session level
NLSSORT function
–
Specifies the type of sort for character data
–
Allows sorts to defined at the query level
Copyright © Oracle Corporation, 2001. All rights reserved.
Using NLS Parameters
in SQL Functions
SELECT TO_CHAR(hire_date,’DD.Mon.YYYY’,
‘NLS_DATE_LANGUAGE=FRENCH’)
FROM
employees;
SELECT ename, TO_CHAR(sal,’9G999D99’,
‘NLS_NUMERIC_CHARACTERS=‘‘,.’’’)
FROM
emp;
18-26
Copyright © Oracle Corporation, 2001. All rights reserved.
Linguistic Index Support
•
•
Linguistic indexing
High performance with local sorting
CREATE INDEX list_word ON
list (NLSSORT(word, ‘NLS_SORT = French_M’));
•
18-30
NLS_COMP parameter for linguistic comparisons
Copyright © Oracle Corporation, 2001. All rights reserved.
Import and Loading Data
Using NLS
18-31
•
Data will be converted from export file character
set to the database character set during the
import.
•
LOADER:
–
Conventional: Data is converted into the session
character set specified by NLS_LANG.
–
DIRECT: Data is converted directly into the
database character set.
Copyright © Oracle Corporation, 2001. All rights reserved.
Obtaining Information
About Character Sets
NLS_DATABASE_PARAMETERS:
18-32
•
PARAMETER
(NLS_CHARACTERSET,
NLS_NCHAR_CHARACTERSET)
•
VALUE
Copyright © Oracle Corporation, 2001. All rights reserved.
Obtaining Information
About NLS Settings
NLS_INSTANCE_PARAMETERS:
•
•
PARAMETER (initialization parameters that have
been explicitly set)
VALUE
NLS_SESSION_PARAMETERS:
•
•
18-33
PARAMETER (session parameters)
VALUE
Copyright © Oracle Corporation, 2001. All rights reserved.
Obtaining Information
About NLS Settings
V$NLS_VALID_VALUES:
•
PARAMETER
(LANGUAGE, SORT, TERRITORY, CHARACTERSET)
•
VALUE
V$NLS_PARAMETERS:
18-35
•
PARAMETER (NLS session parameters,
NLS_CHARACTERSET)
•
VALUE
Copyright © Oracle Corporation, 2001. All rights reserved.
Globalization Support
Utilities
•
•
Character Set Scanner
–
Scans the database to determine if the
character set can be changed
–
Reports are provided detailing possible
problems and fixes
Oracle Locale Builder
–
–
18-37
Easy to use graphical interface
For viewing, modifying, and creating locale
definitions
Copyright © Oracle Corporation, 2001. All rights reserved.
Summary
In this lesson, you should have learned how to:
•
Choose a database character set and a national
character set for the database
•
Use the various types of National Language
Support parameters for the server, or the session
18-38
Copyright © Oracle Corporation, 2001. All rights reserved.
18-39
Copyright © Oracle Corporation, 2001. All rights reserved.
Practice 18 Overview
This practice covers the following topics:
•
•
•
18-40
Checking the database and national character set
Identifying valid NLS values
Setting NLS parameters
Copyright © Oracle Corporation, 2001. All rights reserved.
18-41
Copyright © Oracle Corporation, 2001. All rights reserved.
18-42
Copyright © Oracle Corporation, 2001. All rights reserved.