1/20

Contents

Title and Copyright Information

Preface

• Intended Audience
• Documentation Accessibility
• Related Documentation
• Conventions

Changes in This Release for Oracle Database Globalization Support Guide

• Changes in Oracle Database 12c Release 1 (12.1)

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
  • Subsets and Supersets
• 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
• Choosing a Database Character Set for a Multitenant Container Database
• Changing the Character Set After Database Creation
• Monolingual Database Scenario
  • Character Set Conversion in a Monolingual Scenario
• Multilingual Database Scenario

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 Matching

• Overview of Oracle Database Collation Capabilities
• Using Binary Collation
• Using Linguistic Collation
  • Monolingual Collation
  • Multilingual Collation
    • Multilingual Collation Levels
  • UCA Collation
    • UCA Comparison Levels
• Linguistic Collation Features
  • Base Letters
  • Ignorable Characters
    • Primary Ignorable Characters
    • Secondary Ignorable Characters
    • Tertiary Ignorable Characters
  • Variable Characters and Variable Weighting
    • Examples of Variable Weighting
  • 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 Collation
  • Examples: Case-Insensitive and Accent-Insensitive Collation
  • Specifying a Case-Insensitive or Accent-Insensitive Collation
  • Examples: Linguistic Collation
• Performing Linguistic Comparisons
  • Collation Keys
  • Restricted Precision of Linguistic Comparison
  • Examples: Linguistic Comparison
• 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
    • Use a Tablespace with an Adequate Block Size
    • 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

• What is the Unicode Standard?
• Features of the Unicode Standard
  • Code Points and Supplementary Characters
  • Unicode Encoding Forms
    • UTF-8 Encoding Form
    • UTF-16 Encoding Form
    • UCS-2 Encoding Form
    • UTF-32 Encoding Form
    • CESU-8 Encoding Form
    • Examples: UTF-16, UTF-8, and UCS-2 Encoding
  • Support for the Unicode Standard in Oracle Database
• Implementing a Unicode Solution in the Database
  • Enabling Multilingual Support for Whole Databases
  • Enabling Multilingual Support with Unicode Data Types
  • How to Choose Between Unicode 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
    • LCSScan
  • 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 the Database Migration Assistant for Unicode
  • Migrating Character Data Using a Full Export and Import
• Repairing Database Character Set Metadata
  • Example: Using CSREPAIR
• 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

12 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
• 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 6.2 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
• Upgrading Custom NLB Files from Previous Releases of Oracle Database
• Deploying Custom NLB Files to Oracle Installations on the Same Platform
• Deploying Custom NLB Files to Oracle Installations on Another Platform
• Adding Custom Locale Definitions to Java Components with the GINSTALL Utility
• Customizing Calendars with the NLS Calendar 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
  • Binary Subset-Superset Pairs
• 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