Contents

Title and Copyright Information

Preface

• Intended Audience
• Documentation Accessibility
• Related Documentation
• Conventions

What's New in Globalization Support?

• Oracle Database 11g Release 2 (11.2) New Features in Globalization
• Oracle Database 11g Release 1 (11.1) New Features in Globalization

1 Overview of Globalization Support

• Globalization Support Architecture
  • Locale Data on Demand
  • Architecture to Support Multilingual Applications
  • Using Unicode in a Multilingual Database
• Globalization Support Features
  • Language Support
  • Territory Support
  • Date and Time Formats
  • Monetary and Numeric Formats
  • Calendar Systems
  • Linguistic Sorting
  • Character Set Support
  • Character Semantics
  • Customization of Locale and Calendar Data
  • Unicode Support

2 Choosing a Character Set

• Character Set Encoding
  • What is an Encoded Character Set?
  • Which Characters Are Encoded?
    • Phonetic Writing Systems
    • Ideographic Writing Systems
    • Punctuation, Control Characters, Numbers, and Symbols
    • Writing Direction
  • What Characters Does a Character Set Support?
    • ASCII Encoding
  • How are Characters Encoded?
    • Single-Byte Encoding Schemes
    • Multibyte Encoding Schemes
  • Naming Convention for Oracle Database Character Sets
• Length Semantics
• Choosing an Oracle Database Character Set
  • Current and Future Language Requirements
  • Client Operating System and Application Compatibility
  • Character Set Conversion Between Clients and the Server
  • Performance Implications of Choosing a Database Character Set
  • Restrictions on Database Character Sets
    • Restrictions on Character Sets Used to Express Names
  • Database Character Set Statement of Direction
  • Choosing Unicode as a Database Character Set
  • Choosing a National Character Set
  • Summary of Supported Data Types
• Changing the Character Set After Database Creation
• Monolingual Database Scenario
  • Character Set Conversion in a Monolingual Scenario
• Multilingual Database Scenarios
  • Restricted Multilingual Support
  • Unrestricted Multilingual Support

3 Setting Up a Globalization Support Environment

• Setting NLS Parameters
• Choosing a Locale with the NLS_LANG Environment Variable
  • Specifying the Value of NLS_LANG
  • Overriding Language and Territory Specifications
  • Locale Variants
  • Should the NLS_LANG Setting Match the Database Character Set?
• Character Set Parameter
  • NLS_OS_CHARSET Environment Variable
• NLS Database Parameters
  • NLS Data Dictionary Views
  • NLS Dynamic Performance Views
  • OCINlsGetInfo() Function
• Language and Territory Parameters
  • NLS_LANGUAGE
  • NLS_TERRITORY
    • Overriding Default Values for NLS_LANGUAGE and NLS_TERRITORY During a Session
• Date and Time Parameters
  • Date Formats
    • NLS_DATE_FORMAT
    • NLS_DATE_LANGUAGE
  • Time Formats
    • NLS_TIMESTAMP_FORMAT
    • NLS_TIMESTAMP_TZ_FORMAT
• Calendar Definitions
  • Calendar Formats
    • First Day of the Week
    • First Calendar Week of the Year
    • Number of Days and Months in a Year
    • First Year of Era
  • NLS_CALENDAR
• Numeric and List Parameters
  • Numeric Formats
  • NLS_NUMERIC_CHARACTERS
  • NLS_LIST_SEPARATOR
• Monetary Parameters
  • Currency Formats
  • NLS_CURRENCY
  • NLS_ISO_CURRENCY
  • NLS_DUAL_CURRENCY
  • Oracle Database Support for the Euro
  • NLS_MONETARY_CHARACTERS
  • NLS_CREDIT
  • NLS_DEBIT
• Linguistic Sort Parameters
  • NLS_SORT
  • NLS_COMP
• Character Set Conversion Parameter
  • NLS_NCHAR_CONV_EXCP
• Length Semantics
  • NLS_LENGTH_SEMANTICS

4 Datetime Data Types and Time Zone Support

• Overview of Datetime and Interval Data Types and Time Zone Support
• Datetime and Interval Data Types
  • Datetime Data Types
    • DATE Data Type
    • TIMESTAMP Data Type
    • TIMESTAMP WITH TIME ZONE Data Type
    • TIMESTAMP WITH LOCAL TIME ZONE Data Type
    • Inserting Values into Datetime Data Types
    • Choosing a TIMESTAMP Data Type
  • Interval Data Types
    • INTERVAL YEAR TO MONTH Data Type
    • INTERVAL DAY TO SECOND Data Type
    • Inserting Values into Interval Data Types
• Datetime and Interval Arithmetic and Comparisons
  • Datetime and Interval Arithmetic
  • Datetime Comparisons
  • Explicit Conversion of Datetime Data Types
• Datetime SQL Functions
• Datetime and Time Zone Parameters and Environment Variables
  • Datetime Format Parameters
  • Time Zone Environment Variables
  • Daylight Saving Time Session Parameter
  • Daylight Saving Time Upgrade Parameter
• Choosing a Time Zone File
• Upgrading the Time Zone File and Timestamp with Time Zone Data
  • Daylight Saving Time (DST) Transition Rules Changes
  • Preparing to Upgrade the Time Zone File and Timestamp with Time Zone Data
  • Steps to Upgrade Time Zone File and Timestamp with Time Zone Data
    • Example of Updating Daylight Saving Time Behavior
    • Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data
• Clients and Servers Operating with Different Versions of Time Zone Files
• Setting the Database Time Zone
• Setting the Session Time Zone
• Converting Time Zones With the AT TIME ZONE Clause
• Support for Daylight Saving Time
  • Examples: The Effect of Daylight Saving Time on Datetime Calculations

5 Linguistic Sorting and String Searching

• Overview of Oracle Database Sorting Capabilities
• Using Binary Sorts
• Using Linguistic Sorts
  • Monolingual Linguistic Sorts
  • Multilingual Linguistic Sorts
  • Multilingual Sorting Levels
    • Primary Level Sorts
    • Secondary Level Sorts
    • Tertiary Level Sorts
• Linguistic Sort Features
  • Base Letters
  • Ignorable Characters
  • Contracting Characters
  • Expanding Characters
  • Context-Sensitive Characters
  • Canonical Equivalence
  • Reverse Secondary Sorting
  • Character Rearrangement for Thai and Laotian Characters
  • Special Letters
  • Special Combination Letters
  • Special Uppercase Letters
  • Special Lowercase Letters
• Case-Insensitive and Accent-Insensitive Linguistic Sorts
  • Examples of Case-Insensitive and Accent-Insensitive Sorts
  • Specifying a Case-Insensitive or Accent-Insensitive Sort
  • Linguistic Sort Examples
• Performing Linguistic Comparisons
  • Collation Keys
  • Restricted Precision of Linguistic Comparison
  • Linguistic Comparison Examples
• Using Linguistic Indexes
  • Supported SQL Operations and Functions for Linguistic Indexes
  • Linguistic Indexes for Multiple Languages
  • Requirements for Using Linguistic Indexes
    • Set NLS_SORT Appropriately
    • Specify NOT NULL in a WHERE Clause If the Column Was Not Declared NOT NULL
    • Example: Setting Up a French Linguistic Index
• Searching Linguistic Strings
• SQL Regular Expressions in a Multilingual Environment
  • Character Range '[x-y]' in Regular Expressions
  • Collation Element Delimiter '[. .]' in Regular Expressions
  • Character Class '[: :]' in Regular Expressions
  • Equivalence Class '[= =]' in Regular Expressions
  • Examples: Regular Expressions

6 Supporting Multilingual Databases with Unicode

• Overview of Unicode
• What is Unicode?
  • Supplementary Characters
  • Unicode Encodings
    • UTF-8 Encoding
    • UCS-2 Encoding
    • UTF-16 Encoding
    • Examples: UTF-16, UTF-8, and UCS-2 Encoding
  • Support for Unicode in Oracle Database
• Implementing a Unicode Solution in the Database
  • Enabling Multilingual Support with Unicode Databases
  • Enabling Multilingual Support with Unicode Data Types
  • How to Choose Between a Unicode Database and a Unicode Data Type Solution
    • When Should You Use a Unicode Database?
    • When Should You Use Unicode Data Types?
  • Comparing Unicode Character Sets for Database and Data Type Solutions
• Unicode Case Studies
• Designing Database Schemas to Support Multiple Languages
  • Specifying Column Lengths for Multilingual Data
  • Storing Data in Multiple Languages
    • Store Language Information with the Data
    • Select Translated Data Using Fine-Grained Access Control
  • Storing Documents in Multiple Languages in LOB Data Types
  • Creating Indexes for Searching Multilingual Document Contents
    • Creating Multilexers
    • Creating Indexes for Documents Stored in the CLOB Data Type
    • Creating Indexes for Documents Stored in the BLOB Data Type

7 Programming with Unicode

• Overview of Programming with Unicode
  • Database Access Product Stack and Unicode
• SQL and PL/SQL Programming with Unicode
  • SQL NCHAR Data Types
    • The NCHAR Data Type
    • The NVARCHAR2 Data Type
    • The NCLOB Data Type
  • Implicit Data Type Conversion Between NCHAR and Other Data Types
  • Exception Handling for Data Loss During Data Type Conversion
  • Rules for Implicit Data Type Conversion
  • SQL Functions for Unicode Data Types
  • Other SQL Functions
  • Unicode String Literals
  • NCHAR String Literal Replacement
  • Using the UTL_FILE Package with NCHAR Data
• OCI Programming with Unicode
  • OCIEnvNlsCreate() Function for Unicode Programming
  • OCI Unicode Code Conversion
    • Data Integrity
    • OCI Performance Implications When Using Unicode
    • OCI Unicode Data Expansion
  • Setting UTF-8 to the NLS_LANG Character Set in OCI
  • Binding and Defining SQL CHAR Data Types in OCI
  • Binding and Defining SQL NCHAR Data Types in OCI
  • Handling SQL NCHAR String Literals in OCI
  • Binding and Defining CLOB and NCLOB Unicode Data in OCI
• Pro*C/C++ Programming with Unicode
  • Pro*C/C++ Data Conversion in Unicode
  • Using the VARCHAR Data Type in Pro*C/C++
  • Using the NVARCHAR Data Type in Pro*C/C++
  • Using the UVARCHAR Data Type in Pro*C/C++
• JDBC Programming with Unicode
  • Binding and Defining Java Strings to SQL CHAR Data Types
  • Binding and Defining Java Strings to SQL NCHAR Data Types
    • New JDBC4.0 Methods for NCHAR Data Types
  • Using the SQL NCHAR Data Types Without Changing the Code
  • Using SQL NCHAR String Literals in JDBC
  • Data Conversion in JDBC
    • Data Conversion for the OCI Driver
    • Data Conversion for Thin Drivers
    • Data Conversion for the Server-Side Internal Driver
  • Using oracle.sql.CHAR in Oracle Object Types
    • oracle.sql.CHAR
    • Accessing SQL CHAR and NCHAR Attributes with oracle.sql.CHAR
  • Restrictions on Accessing SQL CHAR Data with JDBC
    • Character Integrity Issues in a Multibyte Database Environment
• ODBC and OLE DB Programming with Unicode
  • Unicode-Enabled Drivers in ODBC and OLE DB
  • OCI Dependency in Unicode
  • ODBC and OLE DB Code Conversion in Unicode
    • OLE DB Code Conversions
  • ODBC Unicode Data Types
  • OLE DB Unicode Data Types
  • ADO Access
• XML Programming with Unicode
  • Writing an XML File in Unicode with Java
  • Reading an XML File in Unicode with Java
  • Parsing an XML Stream in Unicode with Java

8 Oracle Globalization Development Kit

• Overview of the Oracle Globalization Development Kit
• Designing a Global Internet Application
  • Deploying a Monolingual Internet Application
  • Deploying a Multilingual Internet Application
• Developing a Global Internet Application
  • Locale Determination
  • Locale Awareness
  • Localizing the Content
• Getting Started with the Globalization Development Kit
• GDK Quick Start
  • Modifying the HelloWorld Application
• GDK Application Framework for J2EE
  • Making the GDK Framework Available to J2EE Applications
  • Integrating Locale Sources into the GDK Framework
  • Getting the User Locale From the GDK Framework
  • Implementing Locale Awareness Using the GDK Localizer
  • Defining the Supported Application Locales in the GDK
  • Handling Non-ASCII Input and Output in the GDK Framework
  • Managing Localized Content in the GDK
    • Managing Localized Content in JSPs and Java Servlets
    • Managing Localized Content in Static Files
• GDK Java API
  • Oracle Locale Information in the GDK
  • Oracle Locale Mapping in the GDK
  • Oracle Character Set Conversion in the GDK
  • Oracle Date, Number, and Monetary Formats in the GDK
  • Oracle Binary and Linguistic Sorts in the GDK
  • Oracle Language and Character Set Detection in the GDK
  • Oracle Translated Locale and Time Zone Names in the GDK
  • Using the GDK with E-Mail Programs
• The GDK Application Configuration File
  • locale-charset-maps
  • page-charset
  • application-locales
  • locale-determine-rule
  • locale-parameter-name
  • message-bundles
  • url-rewrite-rule
  • Example: GDK Application Configuration File
• GDK for Java Supplied Packages and Classes
  • oracle.i18n.lcsd
  • oracle.i18n.net
  • oracle.i18n.servlet
  • oracle.i18n.text
  • oracle.i18n.util
• GDK for PL/SQL Supplied Packages
• GDK Error Messages

9 SQL and PL/SQL Programming in a Global Environment

• Locale-Dependent SQL Functions with Optional NLS Parameters
  • Default Values for NLS Parameters in SQL Functions
  • Specifying NLS Parameters in SQL Functions
  • Unacceptable NLS Parameters in SQL Functions
• Other Locale-Dependent SQL Functions
  • The CONVERT Function
  • SQL Functions for Different Length Semantics
  • LIKE Conditions for Different Length Semantics
  • Character Set SQL Functions
    • Converting from Character Set Number to Character Set Name
    • Converting from Character Set Name to Character Set Number
    • Returning the Length of an NCHAR Column
  • The NLSSORT Function
    • NLSSORT Syntax
    • Comparing Strings in a WHERE Clause
    • Using the NLS_COMP Parameter to Simplify Comparisons in the WHERE Clause
    • Controlling an ORDER BY Clause
• Miscellaneous Topics for SQL and PL/SQL Programming in a Global Environment
  • SQL Date Format Masks
  • Calculating Week Numbers
  • SQL Numeric Format Masks
  • Loading External BFILE Data into LOB Columns

10 OCI Programming in a Global Environment

• Using the OCI NLS Functions
• Specifying Character Sets in OCI
• Getting Locale Information in OCI
• Mapping Locale Information Between Oracle and Other Standards
• Manipulating Strings in OCI
• Classifying Characters in OCI
• Converting Character Sets in OCI
• OCI Messaging Functions
• lmsgen Utility

11 Character Set Migration

• Overview of Character Set Migration
  • Data Truncation
    • Additional Problems Caused by Data Truncation
  • Character Set Conversion Issues
    • Replacement Characters that Result from Using the Export and Import Utilities
    • Invalid Data That Results from Setting the Client's NLS_LANG Parameter Incorrectly
    • Conversion from Single-byte to Multibyte Character Set and Oracle Data Pump
• Changing the Database Character Set of an Existing Database
  • Migrating Character Data Using a Full Export and Import
  • Migrating a Character Set Using the CSALTER Script
    • Using the CSALTER Script in an Oracle Real Application Clusters Environment
  • Migrating Character Data Using the CSALTER Script and Selective Imports
• Migrating to NCHAR Data Types
  • Migrating Version 8 NCHAR Columns to Oracle9i and Later
  • Changing the National Character Set
  • Migrating CHAR Columns to NCHAR Columns
    • Using the ALTER TABLE MODIFY Statement to Change CHAR Columns to NCHAR Columns
    • Using Online Table Redefinition to Migrate a Large Table to Unicode
• Post-Conversion Considerations After Character Set Migration

12 Character Set Scanner Utilities

• The Language and Character Set File Scanner
  • Syntax of the LCSSCAN Command
  • Examples: Using the LCSSCAN Command
  • Getting Command-Line Help for the Language and Character Set File Scanner
  • Supported Languages and Character Sets
  • LCSSCAN Error Messages
• The Database Character Set Scanner
  • Conversion Tests on Character Data
• Scan Modes in the Database Character Set Scanner
  • Full Database Scan
  • User Scan
  • Table Scan
  • Column Scan
• Installing and Starting the Database Character Set Scanner
  • Access Privileges for the Database Character Set Scanner
  • Installing the Database Character Set Scanner System Tables
  • Starting the Database Character Set Scanner
  • Creating the Database Character Set Scanner Parameter File
  • Getting Command-Line Help for the Database Character Set Scanner
• Database Character Set Scanner Parameters
• Database Character Set Scanner Sessions: Examples
  • Full Database Scan Examples
    • Database Character Set Scanner Messages
  • User Scan Examples
    • Database Character Set Scanner Messages
  • Single Table Scan Examples
    • Database Character Set Scanner Messages
    • Database Character Set Scanner Messages
  • Column Scan Examples
    • Database Character Set Scanner Messages
• Database Character Set Scanner Reports
  • Database Scan Summary Report
    • Database Size
    • Database Scan Parameters
    • Scan Summary
    • Data Dictionary Conversion Summary
    • Application Data Conversion Summary
    • Application Data Conversion Summary Per Column Size Boundary
    • Distribution of Convertible Data Per Table
    • Distribution of Convertible Data Per Column
    • Indexes To Be Rebuilt
    • Truncation Due To Character Semantics
    • Character Set Detection Result
    • Language Detection Result
  • Database Scan Individual Exception Report
    • Database Scan Parameters
    • Data Dictionary Individual Exceptions
    • Application Data Individual Exceptions
• How to Handle Convertible or Lossy Data in the Data Dictionary
• Storage and Performance Considerations in the Database Character Set Scanner
  • Storage Considerations for the Database Character Set Scanner
    • CSM$TABLES
    • CSM$COLUMNS
    • CSM$ERRORS
  • Performance Considerations for the Database Character Set Scanner
    • Using Multiple Scan Processes
    • Setting the Array Fetch Buffer Size
    • Optimizing the QUERY Clause
    • Suppressing Exception and Convertible Log
  • Recommendations and Restrictions for the Database Character Set Scanner
    • Scanning Database Containing Data Not in the Database Character Set
    • Scanning Database Containing Data from Two or More Character Sets
• Database Character Set Scanner CSALTER Script
  • Checking Phase of the CSALTER Script
  • Updating Phase of the CSALTER Script
• Database Character Set Scanner Views
  • CSMV$COLUMNS
  • CSMV$CONSTRAINTS
  • CSMV$ERRORS
  • CSMV$INDEXES
  • CSMV$TABLES
• Database Character Set Scanner Error Messages

13 Customizing Locale Data

• Overview of the Oracle Locale Builder Utility
  • Configuring Unicode Fonts for the Oracle Locale Builder
    • Font Configuration on Windows
    • Font Configuration on Other Platforms
  • The Oracle Locale Builder User Interface
  • Oracle Locale Builder Pages and Dialog Boxes
    • Existing Definitions Dialog Box
    • Session Log Dialog Box
    • Preview NLT Tab Page
    • Open File Dialog Box
• Creating a New Language Definition with Oracle Locale Builder
• Creating a New Territory Definition with the Oracle Locale Builder
  • Customizing Time Zone Data
  • Customizing Calendars with the NLS Calendar Utility
• Displaying a Code Chart with the Oracle Locale Builder
• Creating a New Character Set Definition with the Oracle Locale Builder
  • Character Sets with User-Defined Characters
  • Oracle Database Character Set Conversion Architecture
  • Unicode 5.0 Private Use Area
  • User-Defined Character Cross-References Between Character Sets
  • Guidelines for Creating a New Character Set from an Existing Character Set
  • Example: Creating a New Character Set Definition with the Oracle Locale Builder
• Creating a New Linguistic Sort with the Oracle Locale Builder
  • Changing the Sort Order for All Characters with the Same Diacritic
  • Changing the Sort Order for One Character with a Diacritic
• Generating and Installing NLB Files
• Deploying Custom NLB Files on Other Platforms
• Upgrading Custom NLB Files from Previous Releases of Oracle Database
• Transporting NLB Data from One Platform to Another
• Adding Custom Locale Definitions to Java Components with the GINSTALL Utility

A Locale Data

• Languages
• Translated Messages
• Territories
• Character Sets
  • Recommended Database Character Sets
  • Other Character Sets
  • Character Sets that Support the Euro Symbol
  • Client-Only Character Sets
  • Universal Character Sets
  • Character Set Conversion Support
  • Subsets and Supersets
• Language and Character Set Detection Support
• Linguistic Sorts
• Calendar Systems
• Time Zone Region Names
• Obsolete Locale Data
  • Obsolete Linguistic Sorts
  • Obsolete Territories
  • Obsolete Languages
  • Obsolete Character Sets and Replacement Character Sets
  • AL24UTFFSS Character Set Desupported
  • Updates to the Oracle Database Language and Territory Definition Files

B Unicode Character Code Assignments

• Unicode Code Ranges
• UTF-16 Encoding
• UTF-8 Encoding

Glossary

Index