Transcript Using Globalization Support Features

Using Globalization Support
Copyright © 2004, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to:
• Customize language-dependent behavior for the
database and individual sessions
• Specify different linguistic sorts for queries
• Retrieve data that matches a search string
ignoring case or accent differences
• Obtain Globalization support configuration
information
2-2
Copyright © 2004, Oracle. All rights reserved.
Globalization Support Features
•
•
•
•
•
•
•
•
2-3
Language support
Territory support
Character set support
Linguistic sorting
Message support
Date and time formats
Numeric formats
Monetary formats
Copyright © 2004, Oracle. All rights reserved.
Encoding Schemes
Oracle Database supports different classes of
character encoding schemes:
• Single-byte character sets
– 7-bit
– 8-bit
•
•
•
2-4
Fixed-width multibyte character sets
Varying-width multibyte character sets
Universal character sets, such as Unicode
Copyright © 2004, Oracle. All rights reserved.
Database Character Sets and National
Character Sets
Database Character Sets
2-7
National Character Sets
Defined at creation time
Defined at creation time
Cannot be changed without
re-creation, few exceptions
Can be exchanged
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 © 2004, Oracle. All rights reserved.
Datetimes with Timezones
Datetime Field
Valid Values
YEAR
-4712 to 9999 (excluding 0)
MONTH
01 to 12
DAY
01 to 31
HOUR
00 to 23
MINUTE
00 to 59
SECOND
00 to 59.9 (N) -- N indicates precision
TIMEZONE_HOUR
-12 to 14
TIMEZONE_MINUTE
00 to 59
TIMEZONE_REGION
Valid value in V$TIMEZONE_NAMES
TIMESTAMP '2004-01-31 09:26:56.66 +02:00'
2-9
Copyright © 2004, Oracle. All rights reserved.
Configuring the Database Local Timezone
•
At the session level:
ALTER SESSION SET time_zone = 'Europe/London';
ALTER SESSION SET time_zone = LOCAL;
•
Using an environment variable:
$ export ORA_SDTZ = 'DB_TZ'
•
At the database level:
CREATE DATABASE ...
SET TIME_ZONE='UTC' ...
ALTER DATABASE SET TIME_ZONE='-01:00';
2-10
Copyright © 2004, Oracle. All rights reserved.
Configuring Datetime Formats
•
•
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
Format Element
Definition
FF
Fractional seconds
TZH
Timezone hour
TZM
Timezone minutes
TZR
Timezone region name
TZD
Timezone Daylight savings time
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT =
'YYYY-MM-DD HH:MI:SS.FF TZR TZD';
2-11
Copyright © 2004, Oracle. All rights reserved.
Using Timezones
01-31-04 09:30:00.00 -05:00
01-31-04 09:30:00.00 America/New_York
01-31-04 13:30:00.00 -01:00
CREATE TABLE orders ( ...
orderdate2 TIMESTAMP(3) WITH TIME ZONE ...);
INSERT INTO orders VALUES (...,
'28-OCT-04 11:24:54.000 PM America/New_York',
...);
2-12
Copyright © 2004, Oracle. All rights reserved.
Specifying Language-Dependent Behavior
Initialization parameter
Environment variable
ALTER SESSION
command
SQL function
SELECT sysdate FROM dual;
2-13
Copyright © 2004, Oracle. All rights reserved.
Specifying Language-Dependent Behavior
for the Server
•
NLS_LANGUAGE specifies:
–
–
–
–
–
•
The language for database messages
Day and month names
Symbols for A.D., B.C., a.m., p.m.
The default sorting mechanism
Affirmative and negative response strings
NLS_TERRITORY specifies:
– Day and week numbering
– Credit and debit symbols
– Default date format, decimal character,
group separator, list separator and the default ISO,
dual and local currency symbols
2-14
Copyright © 2004, Oracle. All rights reserved.
Language and Territory Dependent
Parameters
Parameter
NLS_LANGUAGE
NLS_DATE_LANGUAGE
NLS_SORT
Default Values
AMERICAN
AMERICAN
BINARY
NLS_TERRITORY
AMERICA
NLS_CURRENCY
$
NLS_DUAL_CURRENCY
$
NLS_ISO_CURRENCY
AMERICA
NLS_DATE_FORMAT
DD-MON-RR
NLS_NUMERIC_CHARACTERS
.,
NLS_TIMESTAMP_FORMAT
DD-MON-RRHH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RRHH.MI.SSXFF AM TZR
2-15
Copyright © 2004, Oracle. All rights reserved.
Other NLS Server Parameters
Parameter
NLS_CALENDAR
Gregorian
NLS_COMP
2-17
Default Value
BINARY
NLS_LENGTH_SEMANTICS
BYTE
NLS_NCHAR_CONV_EXCP
FALSE
Copyright © 2004, Oracle. All rights reserved.
Specifying Language-Dependent Behavior
for the Session
•
Specify the locale behavior with the NLS_LANG
environment variable:
– Language
– Territory
– Character set
NLS_LANG=FRENCH_CANADA.WE8ISO8859P1
•
Set other NLS environment variables to:
– Override database initialization parameter settings
for all sessions
– Customize the locale behavior
– Change the default location of the NLS library files
2-18
Copyright © 2004, Oracle. 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''') ;
2-20
Copyright © 2004, Oracle. All rights reserved.
Locale Variants
Belgium
Bonjour
2-21
guten Morgen
Copyright © 2004, Oracle. All rights reserved.
Goede
ochtend
Using NLS Parameters in SQL Functions
SELECT TO_CHAR(hire_date,'DD.Mon.YYYY',
'NLS_DATE_LANGUAGE=FRENCH')
FROM employees
WHERE hire_date > '01-JAN-2000';
SELECT last_name, first_name,
TO_CHAR(salary,'99G999D99',
'NLS_NUMERIC_CHARACTERS='',.''')
FROM employees;
2-22
Copyright © 2004, Oracle. All rights reserved.
Using NLS Parameters in SQL Functions
Function
2-24
NLS Parameter
TO_DATE
NLS_DATE_LANGUAGE
NLS_CALENDAR
TO_NUMBER
NLS_NUMERIC_CHARACTERS
NLS_CURRENCY
NLS_[ISO|DUAL]_CURRENCY
TO_CHAR, TO_NCHAR
NLS_DATE_LANGUAGE
NLS_NUMERIC_CHARACTERS
NLS_CURRENCY
NLS_[ISO|DUAL]_CURRENCY
NLS_CALENDAR
NLS_UPPER, NLS_LOWER,
NLS_INITCAP, NLSSORT
NLS_SORT
Copyright © 2004, Oracle. All rights reserved.
Linguistic Sorting
Sort order can be affected by:
• Case sensitivity
• Diacritics or accent characters
• Combination characters that are treated as a
single character
• Phonetics or character appearance
• Cultural preferences
2-25
Copyright © 2004, Oracle. All rights reserved.
Linguistic Sorting
Three types of sorting:
• Binary sorting:
– Sorted according to the binary values of the
encoded characters
•
Monolingual linguistic sorting:
– A two pass sort based on a character’s assigned
major and minor values
•
Multilingual linguistic sorting
– Based on the ISO standard (ISO 14651), and the
Unicode 3.2 Standard for multilingual collation
– Ordered by the number of strokes, PinYin, or
radicals for Chinese characters
2-26
Copyright © 2004, Oracle. All rights reserved.
Using Linguistic Sorting
You can specify the type of sort used for character
data with the:
• NLS_SORT parameter
– Default value is derived from the NLS_LANG
environment variable, if set
– Can be specified for the session, client, or server
•
NLSSORT function
–
2-27
Defines the sorting method at the query level
Copyright © 2004, Oracle. All rights reserved.
Sorts That Are Not Case or
Accent Sensitive
SELECT cust_last_name FROM oe.customers
WHERE cust_last_name = 'de Funes';
SELECT cust_last_name FROM oe.customers
WHERE cust_last_name = NLS_UPPER('de Funes');
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET
NLS_SORT=GENERIC_BASELETTER;
SELECT cust_last_name FROM oe.customers
WHERE cust_last_name = 'De Funes';
2-29
Copyright © 2004, Oracle. All rights reserved.
Linguistic Comparisons
•
Use the NLS_COMP parameter to:
– Perform linguistic comparisons instead of binary
comparisons
– Avoid cumbersome statements involving the
NLSSORT function
•
NLS_COMP can be set to:
– BINARY
– ANSI
SELECT word FROM list
WHERE word > 'gf';
2-30
Copyright © 2004, Oracle. All rights reserved.
Linguistic Index Support
•
Create an index on linguistically sorted values
•
Rapidly query data without having to specify
ORDER BY clause and NLSSORT:
CREATE INDEX list_word ON
list (NLSSORT(word, 'NLS_SORT=French_M'));
SELECT word FROM list;
•
2-31
Set the NLS_SORT parameter to match the
linguistic definition you want to use for the
linguistic sort when creating the index
Copyright © 2004, Oracle. All rights reserved.
Customizing Linguistic Sorting
You can customize linguistic sorting for:
• Ignorable characters
• Contracting or expanding characters
• Special combination letters or special letters
• Expanding characters or special letters
• Special uppercase and lowercase letters
• Context-sensitive characters
• Reverse secondary sorting
• Canonical equivalence
2-32
Copyright © 2004, Oracle. All rights reserved.
Oracle Locale Builder
2-33
Copyright © 2004, Oracle. All rights reserved.
Character Set Scanner Utilities
•
Character Set Scanner:
– Scans the database to determine whether the
character set can be changed
– Provides reports that detail possible problems and
fixes
•
Language and Character Set File Scanner:
– Determines the language and character set for
unknown file text
– Uses probabilities to identify the dominant
language and character set
2-34
Copyright © 2004, Oracle. All rights reserved.
Data Conversion Between Client and
Server Character Sets
CREATE DATABASE ...
CHARACTER SET
WE8ISO8859P1
NATIONAL CHARACTER SET
UTF8
...
% export NLS_LANG=
American_America.US7ASCII
2-36
C:/> set NLS_LANG=
German_Germany.WE8DEC
Copyright © 2004, Oracle. All rights reserved.
NLS Data Conversion with Oracle Utilities
•
•
•
Multiple data conversions can take place when
data is exported from one database and imported
into another if the same character sets are not
used.
External tables use the NLS settings on the server
for determining the data character set.
SQL*Loader:
– Conventional Path: Data is converted into the
session character set specified by NLS_LANG.
– Direct Path: Data is converted using client-side
directives.
2-37
Copyright © 2004, Oracle. All rights reserved.
NLS Data Conversion with Data Pump
•
•
•
2-39
Data Pump Export always saves data in the same
character set as the database from which the data
originates.
Data Pump Import converts the data to the
character set of the target database, if needed.
The Data Pump log file is written in the language
specified by NLS_LANG for the session that started
Data Pump.
Copyright © 2004, Oracle. All rights reserved.
Obtaining Character Set Information
SQL> SELECT parameter, value
2
FROM nls_database_parameters
3
WHERE parameter LIKE '%CHARACTERSET%';
PARAMETER
----------------------NLS_CHARACTERSET
NLS_NCHAR_CHARACTERSET
VALUE
------------WE8ISO8859P1
AL16UTF16
2 rows selected.
2-40
Copyright © 2004, Oracle. All rights reserved.
Obtaining NLS Parameter Information
SQL> ALTER SESSION SET NLS_ISO_CURRENCY=FRANCE;
Session altered.
SQL> SELECT * FROM nls_instance_parameters
2
WHERE parameter LIKE '%ISO%';
PARAMETER
VALUE
----------------------- ------------NLS_ISO_CURRENCY
AMERICA
SQL> SELECT * FROM nls_session_parameters
2
WHERE parameter LIKE '%ISO%';
PARAMETER
VALUE
----------------------- ------------NLS_ISO_CURRENCY
FRANCE
2-41
Copyright © 2004, Oracle. All rights reserved.
Obtaining NLS Settings Information
•
V$NLS_VALID_VALUES:
Contains the values for NLS_LANGUAGE, NLS_SORT,
NLS_TERRITORY and CHARACTERSET that are valid
on your system
•
V$NLS_PARAMETERS:
– Contains the current NLS session settings,
including character sets
– Used as the basis for NLS_SESSION_PARAMETERS
2-42
Copyright © 2004, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Customize language-dependent behavior for the
database and individual sessions
• Specify different linguistic sorts for queries
• Retrieve data that matches a search string
ignoring case or accent differences
• Obtain Globalization support configuration
information
2-43
Copyright © 2004, Oracle. All rights reserved.
Practice 2 Overview:
Using Globalization Support Features
This practice covers the following topics:
• Checking the database and national character set
• Identifying valid NLS values
• Setting NLS parameters
2-44
Copyright © 2004, Oracle. All rights reserved.