Using Globalization Support

1. Using Globalization Support

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

3. Globalization Support Features • Language support • Territory support • Character set support • Linguistic sorting • Message support • Date and time formats • Numeric formats • Monetary formats

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

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

6. 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'

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

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

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

10. Specifying Language-Dependent Behavior Initialization parameter Environment variable ALTER SESSIONcommand SQL function SELECT sysdate FROM dual;

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

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

13. Other NLS Server Parameters

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

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

16. Belgium Bonjour guten Morgen Goede ochtend Locale Variants

17. 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;

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

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

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

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

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

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

24. 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;

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

26. Oracle Locale Builder

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

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

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

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

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

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

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

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

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