Oracle Database - Character Set

> Database > Oracle Database

1 - About

Oracle Database uses the database character set for:

  • Data stored in SQL character datatypes (CHAR, VARCHAR2, CLOB, and LONG).
  • Identifiers such as table names, column names, and PL/SQL variables.
  • Stored SQL and PL/SQL source code, including text literals embedded in this code.

The database character set to be selected is the character set of most clients connecting to this database.

Advertising

3 - Naming Convention

Naming convention for Oracle character set names:

<region><number of bits used to represent a character><standard character set name>[S|C]

4 - Character Set

4.1 - AL32UTF8

Oracle recommends Unicode AL32UTF8 as the database character set. Unicode is the universal character set that supports most of the currently spoken languages of the world.

As AL32UTF8 is a multibyte character set, database operations on character data may be slightly slower when compared to single-byte database character sets, such as WE8MSWIN1252.

Storage space requirements for text in most languages that use characters outside of the ASCII repertoire are higher in AL32UTF8 compared to legacy character sets supporting the language. Note that the increase in storage space concerns only character data and only data that is not in English. The universality and flexibility of Unicode usually outweighs these additional costs.

Legacy character sets should be considered when compatibility, storage requirements, or performance of text processing is critical and the database will ever support only a single group of languages. The database character set to be selected in such case is the character set of most clients connecting to this database.

4.2 - WE8MSWIN1252 (Windows)

For most languages, the default character set is one of the Microsoft Windows character sets, for example WE8MSWIN1252, even though the database is not installed on Windows.

This results from the assumption that most clients connecting to the database run under the Microsoft Windows operating system.

As the database should be able to store all characters coming from the clients and Microsoft Windows character sets have richer character repertoire than the corresponding ISO 8859 character sets, the Microsoft Windows character sets are usually the better choice.

For example, the EE8MSWIN1250 character set supports the Euro currency symbol and various smart quote characters, while the corresponding EE8ISO8859P2 character set does not support them. In any case, Oracle converts the data between the database character set and the client character sets, which are declared by the NLS_LANG settings.

Advertising

4.3 - Installation Default

The default character set suggested or used by Oracle Universal Installer and Database Configuration Assistant in this release is based on the language configuration of the operating system.

5 - How to

5.1 - Get the list of Oracle Character set

SELECT UNIQUE VALUE FROM V$NLS_VALID_VALUES WHERE PARAMETER ='CHARACTERSET' AND isdeprecated = 'FALSE';

See also the documentation to get the full description: Recommended Database Character set in Oracle® Database Globalization Support Guide

5.2 - Get the Id, Get the name

  • The function NLS_CHARSET_ID returns the character set ID number corresponding to character set name string
SELECT NLS_CHARSET_ID('WE8MSWIN1252') FROM dual;
  • the function NLS_CHARSET_NAME function returns the name of the character set corresponding to a specified character set identification number.

5.3 - Get the current database (national) character set

SELECT * FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET%';
PARAMETER                      VALUE                                  
------------------------------ --------------
NLS_NCHAR_CHARACTERSET         AL16UTF16                                
NLS_CHARACTERSET               WE8ISO8859P1                             

6 - Tools

6.1 - Character set scanner

7 - Configuration

7.1 - NLS_CHARACTERSET

The character set (code page) of the localization parameter of the Oracle Database

8 - Documentation / Reference

Advertising
db/oracle/character_set.txt · Last modified: 2017/09/13 16:12 by gerardnico