Using globalization support
Sponsored Links
This presentation is the property of its rightful owner.
1 / 35

Using Globalization Support PowerPoint PPT Presentation


  • 92 Views
  • Uploaded on
  • Presentation posted in: General

Using Globalization Support. 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

Download Presentation

Using Globalization Support

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Using Globalization Support


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


Globalization Support Features

  • Language support

  • Territory support

  • Character set support

  • Linguistic sorting

  • Message support

  • Date and time formats

  • Numeric formats

  • Monetary formats


Encoding Schemes

  • Oracle Database supports different classes of character encoding schemes:

    • Single-byte character sets

      • 7-bit

      • 8-bit

    • Fixed-width multibyte character sets

    • Varying-width multibyte character sets

    • Universal character sets, such as Unicode


  • Database Character Sets

  • 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

Database Character Sets and National Character Sets


  • 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

Datetimes with Timezones

TIMESTAMP '2004-01-31 09:26:56.66 +02:00'


Configuring the Database Local Timezone

  • At the session level:

  • Using an environment variable:

  • At the database level:

ALTER SESSION SET time_zone = 'Europe/London';

ALTER SESSION SET time_zone = LOCAL;

$ export ORA_SDTZ = 'DB_TZ'

CREATE DATABASE ...

SET TIME_ZONE='UTC' ...

ALTER DATABASE SET TIME_ZONE='-01:00';


  • Format Element

  • Definition

  • FF

  • Fractional seconds

  • TZH

  • Timezone hour

  • TZM

  • Timezone minutes

  • TZR

  • Timezone region name

  • TZD

  • Timezone Daylight savings time

Configuring Datetime Formats

  • NLS_TIMESTAMP_FORMAT

  • NLS_TIMESTAMP_TZ_FORMAT

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZR TZD';


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', ...);


Specifying Language-Dependent Behavior

Initialization parameter

Environment variable

ALTER SESSIONcommand

SQL function

SELECT sysdate FROM dual;


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


  • Default Values

AMERICAN

AMERICAN

BINARY

AMERICA

$

$

AMERICA

DD-MON-RR

.,

DD-MON-RRHH.MI.SSXFF AM

DD-MON-RRHH.MI.SSXFF AM TZR

Language and Territory Dependent Parameters

  • Parameter

NLS_LANGUAGE

NLS_DATE_LANGUAGE

NLS_SORT

NLS_TERRITORY

NLS_CURRENCY

NLS_DUAL_CURRENCY

NLS_ISO_CURRENCY

NLS_DATE_FORMAT

NLS_NUMERIC_CHARACTERS

NLS_TIMESTAMP_FORMAT

NLS_TIMESTAMP_TZ_FORMAT


Other NLS Server Parameters


Specifying Language-Dependent Behavior for the Session

  • Specify the locale behavior with the NLS_LANG environment variable:

    • Language

    • Territory

    • Character set

  • 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

NLS_LANG=FRENCH_CANADA.WE8ISO8859P1


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''') ;


Belgium

Bonjour

guten Morgen

Goede ochtend

Locale Variants


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;


Using NLS Parameters in SQL Functions

  • Function

  • 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


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


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


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

      • Defines the sorting method at the query level


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';


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';


Linguistic Index Support

  • Create an index on linguistically sorted values

  • Rapidly query data without having to specify ORDERBY clause and NLSSORT:

  • Set the NLS_SORT parameter to match the linguistic definition you want to use for the linguistic sort when creating the index

CREATE INDEX list_word ON

list (NLSSORT(word, 'NLS_SORT=French_M'));

SELECT word FROM list;


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


Oracle Locale Builder


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


Data Conversion Between Client and Server Character Sets

CREATE DATABASE ...

CHARACTER SET WE8ISO8859P1

NATIONAL CHARACTER SET

UTF8

...

% export NLS_LANG= American_America.US7ASCII

C:/> set NLS_LANG= German_Germany.WE8DEC


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.


NLS Data Conversion with Data Pump

  • 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.


Obtaining Character Set Information

SQL> SELECT parameter, value 2 FROM nls_database_parameters

3 WHERE parameter LIKE '%CHARACTERSET%';

PARAMETER VALUE

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

NLS_CHARACTERSET WE8ISO8859P1

NLS_NCHAR_CHARACTERSET AL16UTF16

2 rows selected.


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


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


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


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


  • Login