Go to main content
1/21
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 12
c
Release 2 (12.2)
New Features
Deprecated Features
Changes in Oracle Database 12
c
Release 1 (12.1)
New Features
Desupported Features
1
Overview of Globalization Support
1.1
Globalization Support Architecture
1.1.1
Locale Data on Demand
1.1.2
Architecture to Support Multilingual Applications
1.1.3
Using Unicode in a Multilingual Database
1.2
Globalization Support Features
1.2.1
Language Support
1.2.2
Territory Support
1.2.3
Date and Time Formats
1.2.4
Monetary and Numeric Formats
1.2.5
Calendar Systems
1.2.6
Linguistic Sorting
1.2.7
Character Set Support
1.2.8
Character Semantics
1.2.9
Customization of Locale and Calendar Data
1.2.10
Unicode Support
2
Choosing a Character Set
2.1
Character Set Encoding
2.1.1
What is an Encoded Character Set?
2.1.2
Which Characters Are Encoded?
2.1.2.1
Phonetic Writing Systems
2.1.2.2
Ideographic Writing Systems
2.1.2.3
Punctuation, Control Characters, Numbers, and Symbols
2.1.2.4
Writing Direction
2.1.3
What Characters Does a Character Set Support?
2.1.3.1
ASCII Encoding
2.1.4
How are Characters Encoded?
2.1.4.1
Single-Byte Encoding Schemes
2.1.4.2
Multibyte Encoding Schemes
2.1.5
Naming Convention for Oracle Database Character Sets
2.1.6
Subsets and Supersets
2.2
Length Semantics
2.3
Choosing an Oracle Database Character Set
2.3.1
Current and Future Language Requirements
2.3.2
Client Operating System and Application Compatibility
2.3.3
Character Set Conversion Between Clients and the Server
2.3.4
Performance Implications of Choosing a Database Character Set
2.3.5
Restrictions on Database Character Sets
2.3.5.1
Restrictions on Character Sets Used to Express Names
2.3.6
Database Character Set Statement of Direction
2.3.7
Choosing Unicode as a Database Character Set
2.3.8
Choosing a National Character Set
2.3.9
Summary of Supported Data Types
2.4
Choosing a Database Character Set for a Multitenant Container Database
2.5
Changing the Character Set After Database Creation
2.6
Monolingual Database Scenario
2.6.1
Character Set Conversion in a Monolingual Scenario
2.7
Multilingual Database Scenario
3
Setting Up a Globalization Support Environment
3.1
Setting NLS Parameters
3.2
Choosing a Locale with the NLS_LANG Environment Variable
3.2.1
Specifying the Value of NLS_LANG
3.2.2
Overriding Language and Territory Specifications
3.2.3
Locale Variants
3.2.4
Should the NLS_LANG Setting Match the Database Character Set?
3.3
Character Set Parameter
3.3.1
NLS_OS_CHARSET Environment Variable
3.4
NLS Database Parameters
3.4.1
NLS Data Dictionary Views
3.4.2
NLS Dynamic Performance Views
3.4.3
OCINlsGetInfo() Function
3.5
Language and Territory Parameters
3.5.1
NLS_LANGUAGE
3.5.2
NLS_TERRITORY
3.5.2.1
Overriding Default Values for NLS_LANGUAGE and NLS_TERRITORY During a Session
3.6
Date and Time Parameters
3.6.1
Date Formats
3.6.1.1
NLS_DATE_FORMAT
3.6.1.2
NLS_DATE_LANGUAGE
3.6.2
Time Formats
3.6.2.1
NLS_TIMESTAMP_FORMAT
3.6.2.2
NLS_TIMESTAMP_TZ_FORMAT
3.7
Calendar Definitions
3.7.1
Calendar Formats
3.7.1.1
First Day of the Week
3.7.1.2
First Calendar Week of the Year
3.7.1.3
Number of Days and Months in a Year
3.7.1.4
First Year of Era
3.7.2
NLS_CALENDAR
3.8
Numeric and List Parameters
3.8.1
Numeric Formats
3.8.2
NLS_NUMERIC_CHARACTERS
3.8.3
NLS_LIST_SEPARATOR
3.9
Monetary Parameters
3.9.1
Currency Formats
3.9.2
NLS_CURRENCY
3.9.3
NLS_ISO_CURRENCY
3.9.4
NLS_DUAL_CURRENCY
3.9.5
Oracle Database Support for the Euro
3.9.6
NLS_MONETARY_CHARACTERS
3.9.7
NLS_CREDIT
3.9.8
NLS_DEBIT
3.10
Linguistic Sort Parameters
3.10.1
NLS_SORT
3.10.2
NLS_COMP
3.11
Character Set Conversion Parameter
3.11.1
NLS_NCHAR_CONV_EXCP
3.12
Length Semantics
3.12.1
NLS_LENGTH_SEMANTICS
4
Datetime Data Types and Time Zone Support
4.1
Overview of Datetime and Interval Data Types and Time Zone Support
4.2
Datetime and Interval Data Types
4.2.1
Datetime Data Types
4.2.1.1
DATE Data Type
4.2.1.2
TIMESTAMP Data Type
4.2.1.3
TIMESTAMP WITH TIME ZONE Data Type
4.2.1.4
TIMESTAMP WITH LOCAL TIME ZONE Data Type
4.2.1.5
Inserting Values into Datetime Data Types
4.2.1.6
Choosing a TIMESTAMP Data Type
4.2.2
Interval Data Types
4.2.2.1
INTERVAL YEAR TO MONTH Data Type
4.2.2.2
INTERVAL DAY TO SECOND Data Type
4.2.2.3
Inserting Values into Interval Data Types
4.3
Datetime and Interval Arithmetic and Comparisons
4.3.1
Datetime and Interval Arithmetic
4.3.2
Datetime Comparisons
4.3.3
Explicit Conversion of Datetime Data Types
4.4
Datetime SQL Functions
4.5
Datetime and Time Zone Parameters and Environment Variables
4.5.1
Datetime Format Parameters
4.5.2
Time Zone Environment Variables
4.5.3
Daylight Saving Time Session Parameter
4.5.4
Daylight Saving Time Upgrade Parameter
4.6
Choosing a Time Zone File
4.7
Upgrading the Time Zone File and Timestamp with Time Zone Data
4.7.1
Daylight Saving Time (DST) Transition Rules Changes
4.7.2
Preparing to Upgrade the Time Zone File and Timestamp with Time Zone Data
4.7.3
Steps to Upgrade Time Zone File and Timestamp with Time Zone Data
4.7.3.1
Example of Updating Daylight Saving Time Behavior
4.7.3.2
Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data
4.8
Clients and Servers Operating with Different Versions of Time Zone Files
4.9
Setting the Database Time Zone
4.10
Setting the Session Time Zone
4.11
Converting Time Zones With the AT TIME ZONE Clause
4.12
Support for Daylight Saving Time
4.12.1
Examples: The Effect of Daylight Saving Time on Datetime Calculations
5
Linguistic Sorting and Matching
5.1
Overview of Oracle Database Collation Capabilities
5.2
Using Binary Collation
5.3
Using Linguistic Collation
5.3.1
Monolingual Collation
5.3.2
Multilingual Collation
5.3.2.1
Multilingual Collation Levels
5.3.2.1.1
Primary Level Collation
5.3.2.1.2
Secondary Level Collation
5.3.2.1.3
Tertiary Level Collation
5.3.3
UCA Collation
5.3.3.1
UCA Comparison Levels
5.3.3.1.1
Primary Level
5.3.3.1.2
Secondary Level
5.3.3.1.3
Tertiary Level
5.3.3.1.4
Quaternary Level
5.3.3.2
UCA Collation Parameters
5.4
Linguistic Collation Features
5.4.1
Base Letters
5.4.2
Ignorable Characters
5.4.2.1
Primary Ignorable Characters
5.4.2.2
Secondary Ignorable Characters
5.4.2.3
Tertiary Ignorable Characters
5.4.3
Variable Characters and Variable Weighting
5.4.4
Contracting Characters
5.4.5
Expanding Characters
5.4.6
Context-Sensitive Characters
5.4.7
Canonical Equivalence
5.4.8
Reverse Secondary Sorting
5.4.9
Character Rearrangement for Thai and Laotian Characters
5.4.10
Special Letters
5.4.11
Special Combination Letters
5.4.12
Special Uppercase Letters
5.4.13
Special Lowercase Letters
5.5
Case-Insensitive and Accent-Insensitive Linguistic Collation
5.5.1
Examples: Case-Insensitive and Accent-Insensitive Collation
5.5.2
Specifying a Case-Insensitive or Accent-Insensitive Collation
5.5.3
Examples: Linguistic Collation
5.6
Performing Linguistic Comparisons
5.6.1
Collation Keys
5.6.2
Restricted Precision of Linguistic Comparison
5.6.3
Avoiding ORA-12742 Error
5.6.4
Examples: Linguistic Comparison
5.7
Using Linguistic Indexes
5.7.1
Supported SQL Operations and Functions for Linguistic Indexes
5.7.2
Linguistic Indexes for Multiple Languages
5.7.3
Requirements for Using Linguistic Indexes
5.7.3.1
Set NLS_SORT Appropriately
5.7.3.2
Specify NOT NULL in a WHERE Clause If the Column Was Not Declared NOT NULL
5.7.3.3
Use a Tablespace with an Adequate Block Size
5.7.3.4
Example: Setting Up a French Linguistic Index
5.8
Searching Linguistic Strings
5.9
SQL Regular Expressions in a Multilingual Environment
5.9.1
Character Range '[x-y]' in Regular Expressions
5.9.2
Collation Element Delimiter '[. .]' in Regular Expressions
5.9.3
Character Class '[: :]' in Regular Expressions
5.9.4
Equivalence Class '[= =]' in Regular Expressions
5.9.5
Examples: Regular Expressions
5.10
Column-Level Collation and Case Sensitivity
5.10.1
About Data-Bound Collation
5.10.2
Default Collations
5.10.3
Enabling Data-Bound Collation
5.10.4
Specifying a Data-Bound Collation
5.10.4.1
Effective Schema Default Collation
5.10.4.2
Specifying Data-Bound Collation for a Schema
5.10.4.3
Specifying Data-Bound Collation for a Table
5.10.4.4
Specifying Data-Bound Collation for a View and a Materialized View
5.10.4.5
Specifying Data-Bound Collation for a Column
5.10.4.6
Specifying Data-Bound Collation for PL/SQL Units
5.10.4.7
Specifying Data-Bound Collation for SQL Expressions
5.10.4.7.1
Collation Derivation
5.10.4.7.2
Collation Determination
5.10.4.7.3
Expression Evaluation and the COLLATE Operator
5.10.4.7.4
COLLATION Function
5.10.4.7.5
NLS_COLLATION_ID and NLS_COLLATION_NAME Functions
5.10.5
Case-Insensitive Database
5.10.6
Effect of Data-Bound Collation on Other Database Objects
5.10.7
Effect of Data-Bound Collation on Distributed Queries and DML Operations
5.10.8
Effect of Data-Bound Collation on PL/SQL Types and User-Defined Types
5.10.9
Effect of Data-Bound Collation on Oracle XML DB
6
Supporting Multilingual Databases with Unicode
6.1
What is the Unicode Standard?
6.2
Features of the Unicode Standard
6.2.1
Code Points and Supplementary Characters
6.2.2
Unicode Encoding Forms
6.2.2.1
UTF-8 Encoding Form
6.2.2.2
UTF-16 Encoding Form
6.2.2.3
UCS-2 Encoding Form
6.2.2.4
UTF-32 Encoding Form
6.2.2.5
CESU-8 Encoding Form
6.2.2.6
Examples: UTF-16, UTF-8, and UCS-2 Encoding
6.2.3
Support for the Unicode Standard in Oracle Database
6.3
Implementing a Unicode Solution in the Database
6.3.1
Enabling Multilingual Support for a Database
6.3.2
Enabling Multilingual Support with Unicode Data Types
6.3.3
How to Choose Between Unicode Solutions
6.4
Unicode Case Studies
6.5
Designing Database Schemas to Support Multiple Languages
6.5.1
Specifying Column Lengths for Multilingual Data
6.5.2
Storing Data in Multiple Languages
6.5.3
Storing Documents in Multiple Languages in LOB Data Types
6.5.4
Creating Indexes for Searching Multilingual Document Contents
6.5.4.1
Creating Multilexers
6.5.4.2
Creating Indexes for Documents Stored in the CLOB Data Type
6.5.4.3
Creating Indexes for Documents Stored in the BLOB Data Type
7
Programming with Unicode
7.1
Overview of Programming with Unicode
7.1.1
Database Access Product Stack and Unicode
7.2
SQL and PL/SQL Programming with Unicode
7.2.1
SQL NCHAR Data Types
7.2.1.1
The NCHAR Data Type
7.2.1.2
The NVARCHAR2 Data Type
7.2.1.3
The NCLOB Data Type
7.2.2
Implicit Data Type Conversion Between NCHAR and Other Data Types
7.2.3
Exception Handling for Data Loss During Data Type Conversion
7.2.4
Rules for Implicit Data Type Conversion
7.2.5
SQL Functions for Unicode Data Types
7.2.6
Other SQL Functions
7.2.7
Unicode String Literals
7.2.8
NCHAR String Literal Replacement
7.2.9
Using the UTL_FILE Package with NCHAR Data
7.3
OCI Programming with Unicode
7.3.1
OCIEnvNlsCreate() Function for Unicode Programming
7.3.2
OCI Unicode Code Conversion
7.3.2.1
Data Integrity
7.3.2.2
OCI Performance Implications When Using Unicode
7.3.2.3
OCI Unicode Data Expansion
7.3.3
Setting UTF-8 to the NLS_LANG Character Set in OCI
7.3.4
Binding and Defining SQL CHAR Data Types in OCI
7.3.5
Binding and Defining SQL NCHAR Data Types in OCI
7.3.6
Handling SQL NCHAR String Literals in OCI
7.3.7
Binding and Defining CLOB and NCLOB Unicode Data in OCI
7.4
Pro*C/C++ Programming with Unicode
7.4.1
Pro*C/C++ Data Conversion in Unicode
7.4.2
Using the VARCHAR Data Type in Pro*C/C++
7.4.3
Using the NVARCHAR Data Type in Pro*C/C++
7.4.4
Using the UVARCHAR Data Type in Pro*C/C++
7.5
JDBC Programming with Unicode
7.5.1
Binding and Defining Java Strings to SQL CHAR Data Types
7.5.2
Binding and Defining Java Strings to SQL NCHAR Data Types
7.5.2.1
New JDBC4.0 Methods for NCHAR Data Types
7.5.3
Using the SQL NCHAR Data Types Without Changing the Code
7.5.4
Using SQL NCHAR String Literals in JDBC
7.5.5
Data Conversion in JDBC
7.5.5.1
Data Conversion for the OCI Driver
7.5.5.2
Data Conversion for Thin Drivers
7.5.5.3
Data Conversion for the Server-Side Internal Driver
7.5.6
Using oracle.sql.CHAR in Oracle Object Types
7.5.6.1
oracle.sql.CHAR
7.5.6.2
Accessing SQL CHAR and NCHAR Attributes with oracle.sql.CHAR
7.5.7
Restrictions on Accessing SQL CHAR Data with JDBC
7.5.7.1
Character Integrity Issues in a Multibyte Database Environment
7.6
ODBC and OLE DB Programming with Unicode
7.6.1
Unicode-Enabled Drivers in ODBC and OLE DB
7.6.2
OCI Dependency in Unicode
7.6.3
ODBC and OLE DB Code Conversion in Unicode
7.6.3.1
OLE DB Code Conversions
7.6.4
ODBC Unicode Data Types
7.6.5
OLE DB Unicode Data Types
7.6.6
ADO Access
7.7
XML Programming with Unicode
7.7.1
Writing an XML File in Unicode with Java
7.7.2
Reading an XML File in Unicode with Java
7.7.3
Parsing an XML Stream in Unicode with Java
8
Oracle Globalization Development Kit
8.1
Overview of the Oracle Globalization Development Kit
8.2
Designing a Global Internet Application
8.2.1
Deploying a Monolingual Internet Application
8.2.2
Deploying a Multilingual Internet Application
8.3
Developing a Global Internet Application
8.3.1
Locale Determination
8.3.2
Locale Awareness
8.3.3
Localizing the Content
8.4
Getting Started with the Globalization Development Kit
8.5
GDK Quick Start
8.5.1
Modifying the HelloWorld Application
8.6
GDK Application Framework for J2EE
8.6.1
Making the GDK Framework Available to J2EE Applications
8.6.2
Integrating Locale Sources into the GDK Framework
8.6.3
Getting the User Locale From the GDK Framework
8.6.4
Implementing Locale Awareness Using the GDK Localizer
8.6.5
Defining the Supported Application Locales in the GDK
8.6.6
Handling Non-ASCII Input and Output in the GDK Framework
8.6.7
Managing Localized Content in the GDK
8.6.7.1
Managing Localized Content in JSPs and Java Servlets
8.6.7.2
Managing Localized Content in Static Files
8.7
GDK Java API
8.7.1
Oracle Locale Information in the GDK
8.7.2
Oracle Locale Mapping in the GDK
8.7.3
Oracle Character Set Conversion in the GDK
8.7.4
Oracle Date, Number, and Monetary Formats in the GDK
8.7.5
Oracle Binary and Linguistic Sorts in the GDK
8.7.6
Oracle Language and Character Set Detection in the GDK
8.7.7
Oracle Translated Locale and Time Zone Names in the GDK
8.7.8
Using the GDK with E-Mail Programs
8.8
The GDK Application Configuration File
8.8.1
locale-charset-maps
8.8.2
page-charset
8.8.3
application-locales
8.8.4
locale-determine-rule
8.8.5
locale-parameter-name
8.8.6
message-bundles
8.8.7
url-rewrite-rule
8.8.8
Example: GDK Application Configuration File
8.9
GDK for Java Supplied Packages and Classes
8.9.1
oracle.i18n.lcsd
8.9.1.1
LCSScan
8.9.1.1.1
Syntax of the LCSScan Command
8.9.1.1.2
Examples of Using LCSScan
8.9.2
oracle.i18n.net
8.9.3
oracle.i18n.servlet
8.9.4
oracle.i18n.text
8.9.5
oracle.i18n.util
8.10
GDK for PL/SQL Supplied Packages
8.11
GDK Error Messages
9
SQL and PL/SQL Programming in a Global Environment
9.1
Locale-Dependent SQL Functions with Optional NLS Parameters
9.1.1
Default Values for NLS Parameters in SQL Functions
9.1.2
Specifying NLS Parameters in SQL Functions
9.1.3
Unacceptable NLS Parameters in SQL Functions
9.2
Other Locale-Dependent SQL Functions
9.2.1
The CONVERT Function
9.2.2
SQL Functions for Different Length Semantics
9.2.3
LIKE Conditions for Different Length Semantics
9.2.4
Character Set SQL Functions
9.2.4.1
Converting from Character Set Number to Character Set Name
9.2.4.2
Converting from Character Set Name to Character Set Number
9.2.4.3
Returning the Length of an NCHAR Column
9.2.5
The NLSSORT Function
9.2.5.1
NLSSORT Syntax
9.2.5.2
Comparing Strings in a WHERE Clause
9.2.5.3
Controlling an ORDER BY Clause
9.3
Miscellaneous Topics for SQL and PL/SQL Programming in a Global Environment
9.3.1
SQL Date Format Masks
9.3.2
Calculating Week Numbers
9.3.3
SQL Numeric Format Masks
9.3.4
Loading External BFILE Data into LOB Columns
10
OCI Programming in a Global Environment
10.1
Using the OCI NLS Functions
10.2
Specifying Character Sets in OCI
10.3
Getting Locale Information in OCI
10.4
Mapping Locale Information Between Oracle and Other Standards
10.5
Manipulating Strings in OCI
10.6
Classifying Characters in OCI
10.7
Converting Character Sets in OCI
10.8
OCI Messaging Functions
10.9
lmsgen Utility
11
Character Set Migration
11.1
Overview of Character Set Migration
11.1.1
Data Truncation
11.1.1.1
Additional Problems Caused by Data Truncation
11.1.2
Character Set Conversion Issues
11.1.2.1
Replacement Characters that Result from Using the Export and Import Utilities
11.1.2.2
Invalid Data That Results from Setting the Client's NLS_LANG Parameter Incorrectly
11.1.2.3
Conversion from Single-byte to Multibyte Character Set and Oracle Data Pump
11.2
Changing the Database Character Set of an Existing Database
11.2.1
Migrating Character Data Using the Database Migration Assistant for Unicode
11.2.2
Migrating Character Data Using a Full Export and Import
11.3
Repairing Database Character Set Metadata
11.3.1
Example: Using CSREPAIR
11.4
The Language and Character Set File Scanner
11.4.1
Syntax of the LCSSCAN Command
11.4.2
Examples: Using the LCSSCAN Command
11.4.3
Getting Command-Line Help for the Language and Character Set File Scanner
11.4.4
Supported Languages and Character Sets
11.4.5
LCSSCAN Error Messages
12
Customizing Locale Data
12.1
Overview of the Oracle Locale Builder Utility
12.1.1
Configuring Unicode Fonts for the Oracle Locale Builder
12.1.2
The Oracle Locale Builder User Interface
12.1.3
Oracle Locale Builder Pages and Dialog Boxes
12.1.3.1
Existing Definitions Dialog Box
12.1.3.2
Session Log Dialog Box
12.1.3.3
Preview NLT Tab Page
12.1.3.4
Open File Dialog Box
12.2
Creating a New Language Definition with Oracle Locale Builder
12.3
Creating a New Territory Definition with the Oracle Locale Builder
12.4
Displaying a Code Chart with the Oracle Locale Builder
12.5
Creating a New Character Set Definition with the Oracle Locale Builder
12.5.1
Character Sets with User-Defined Characters
12.5.2
Oracle Database Character Set Conversion Architecture
12.5.3
Unicode 7.0 Private Use Area
12.5.4
User-Defined Character Cross-References Between Character Sets
12.5.5
Guidelines for Creating a New Character Set from an Existing Character Set
12.5.6
Example: Creating a New Character Set Definition with the Oracle Locale Builder
12.6
Creating a New Linguistic Sort with the Oracle Locale Builder
12.6.1
Changing the Sort Order for All Characters with the Same Diacritic
12.6.2
Changing the Sort Order for One Character with a Diacritic
12.7
Generating and Installing NLB Files
12.8
Upgrading Custom NLB Files from Previous Releases of Oracle Database
12.9
Deploying Custom NLB Files to Oracle Installations on the Same Platform
12.10
Deploying Custom NLB Files to Oracle Installations on Another Platform
12.11
Adding Custom Locale Definitions to Java Components with the GINSTALL Utility
12.12
Customizing Calendars with the NLS Calendar Utility
A
Locale Data
A.1
Languages
A.2
Translated Messages
A.3
Territories
A.4
Character Sets
A.4.1
Recommended Database Character Sets
A.4.2
Other Character Sets
A.4.3
Character Sets that Support the Euro Symbol
A.4.4
Client-Only Character Sets
A.4.5
Universal Character Sets
A.4.6
Character Set Conversion Support
A.4.7
Binary Subset-Superset Pairs
A.5
Language and Character Set Detection Support
A.6
Linguistic Collations
A.7
Calendar Systems
A.8
Time Zone Region Names
A.9
Obsolete Locale Data
A.9.1
Obsolete Linguistic Sorts
A.9.2
Obsolete Territories
A.9.3
Obsolete Languages
A.9.4
Obsolete Character Sets and Replacement Character Sets
A.9.5
AL24UTFFSS Character Set Desupported
A.9.6
Updates to the Oracle Database Language and Territory Definition Files
B
Unicode Character Code Assignments
B.1
Unicode Code Ranges
B.2
UTF-16 Encoding
B.3
UTF-8 Encoding
C
Collation Derivation and Determination Rules for SQL Operations
C.1
Collation Derivation
C.2
Collation Determination
C.3
SQL Operations and Their Derivation- and Determination-relevant Arguments
Glossary
Index
Scripting on this page enhances content navigation, but does not change the content in any way.