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

Using Globalization Support PowerPoint PPT Presentation


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

Using Globalization Support


Objectives

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

Globalization Support Features

  • Language support

  • Territory support

  • Character set support

  • Linguistic sorting

  • Message support

  • Date and time formats

  • Numeric formats

  • Monetary formats


Encoding schemes

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 and national character sets

  • 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


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

Datetimes with Timezones

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


Configuring the database local timezone

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


Configuring datetime formats

  • 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

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

Specifying Language-Dependent Behavior

Initialization parameter

Environment variable

ALTER SESSIONcommand

SQL function

SELECT sysdate FROM dual;


Specifying language dependent behavior for the server

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


Language and territory dependent parameters

  • 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

Other NLS Server Parameters


Specifying language dependent behavior for the session

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 session1

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


Locale variants

Belgium

Bonjour

guten Morgen

Goede ochtend

Locale Variants


Using nls parameters in sql functions

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 functions1

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

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 sorting1

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

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

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

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

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

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

Oracle Locale Builder


Character set scanner utilities

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

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

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

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

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

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

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

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

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