Unicode on Oracle

Note: If you are unfamiliar with Unicode see the brief primer at this link before continuing.

Oracle Character Sets

Unicode characters are stored in an Oracle database using one of Oracle's Unicode character sets. An Oracle character set defines a group of characters with the following common features.

  • same region of the world
  • same encoding
  • same code point map

The following table contains a sample of Unicode character sets available in a Oracle database as well as some older character sets commonly used before Unicode was invented.

Oracle Character Set Name
Region IANA Character Set Name
Can be Used as
Database Character Set
National Character Set
  • the default database character set for new installations
  • this is Oracle's first recommended database character set choice for new system deployment
  • the default national character set for new installations
  • processing AL16UTF16 strings is typically faster than processing UTF8 strings because Oracle processes most AL16UTF16 characters as fixed-width characters
  • can only be used for character set conversion
  • exists for backward compatibility with Oracle 8 databases
WE8ISO8859P15 Western Europe
ISO-8859-15 Y
  • many character sets are a superset of US7ASCII; i.e. the first 128 characters of the superset are the same as US7ASCII
Database Character Set

Much of the following information was taken from Oracle Database Globalization Support Guide - Choosing a Character Set.

Oracle uses the "database character" set for:

  • storing data in SQL CHAR datatypes (CHAR, VARCHAR2, CLOB, and LONG)
  • storing table names, column names, and PL/SQL variable names
  • storing SQL and PL/SQL source code.

The database character set is defined using the CREATE DATABASE statement. The database parameter NLS_CHARACTERSET identifies the database character set used on a given database.

The database character set must be a superset of either EBCDIC or 7-bit ASCII, whichever is the native character set on the platform. It is therfore not possible to use a fixed-width, multibyte character set as the database character set.

In some cases it is possible to change the database character set after the database has been created. In most of these cases a full export/import will be required to do the conversion.

National Character Set

The national character set is used for data stored inside NCHAR, NVARCHAR, NCLOB columns. It is defined using the CREATE DATABASE statement. The database parameter NLS_NCHAR_CHARACTERSET identifies the national character set used on a given database.

The national character set can be either UTF8 or AL16UTF16. The default is AL16UTF16.

Values returned by Oracle's UNISTR() function are always in the national character set.

Determining the Character Sets in Use for a Database

To determine the database character set and the national character set for a given database use the following query.

select parameter, value
from   nls_database_parameters
where  parameter like 'NLS%CHARACTERSET' ;
PARAMETER                      VALUE
------------------------------ ---------------

The CHR(n) Function

Oracle's CHR function takes a numeric argument n and returns the VARCHAR2 value that is the binary equivalent of n in the database character set (or the national character set if USING NCHAR_CS is specified). Since one character set's binary representation of a character can differ from another character set's binary representation CHR is a poor choice for representing Unicode characters in SQL or PL/SQL code.

For example, say we wanted to write a query that returned values containing the copyright symbol © (U+00A9). If we were working on a database whose database character set was WE8ISO8859P1 a WHERE clause like this would work.

where description like '%' || chr(169) || '%'

However, if the code were later moved to a database with a database character set of AL32UTF8 the WHERE clause would no longer function properly. On an AL32UTF8 database the copyright symbol has a binary representation of 49833, not 169.

In place of CHR programmers can use the UNISTR function. This function's return value depends on an encoded string, not a binary value. Thus, a WHERE clause like the following will work on any database regardless of its database character set.

where description like unistr( '%\00A9%' )

Test Query

The following query returns text in many popular languages. It can be used to test an application's support for Unicode characters.

  'Arabic     : '|| unistr( '\0627\0644\0639\0631\0628\064A\0629' )      || '
  Chinese    : ' || unistr( '\4E2D\6587' )                               || '
  English    : ' || unistr( 'English' )                                  || '
  French     : ' || unistr( 'Fran\00E7ais' )                             || '
  German     : ' || unistr( 'Deutsch' )                                  || '
  Greek      : ' || unistr( '\0395\03BB\03BB\03B7\03BD\03B9\03BA\03AC' ) || '
  Hebrew     : ' || unistr( '\05E2\05D1\05E8\05D9\05EA' )                || '
  Japanese   : ' || unistr( '\65E5\672C\8A9E' )                          || '
  Korean     : ' || unistr( '\D55C\AD6D\C5B4' )                          || '
  Portuguese : ' || unistr( 'Portugu\00EAs' )                            || '
  Russian    : ' || unistr( '\0420\0443\0441\0441\043A\0438\0439' )      || '
  Spanish    : ' || unistr( 'Espa\00F1ol' )                              || '
  Thai       : ' || unistr( '\0E44\0E17\0E22' )
  as unicode_test_string
from dual ;

In an application that fully supports all the test characters the output will look like this.

Arabic     : العربية
Chinese    : 中文
English    : English
French     : Français
German     : Deutsch
Greek      : Ελληνικά
Hebrew     : עברית
Japanese   : 日本語
Korean     : 한국어
Portuguese : Português
Russian    : Русский
Spanish    : Español
Thai       : ไทย

If any characters are missing or garbled in your application it may indicate the application does not use font substitution. Alternatively it may indicate the application does use font substitution but requires additional fonts.

You can also cut and paste the sample output above into non-database applications such as editors or terminal emulators to test the range of characters they support.

Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-13406.html]SQL Snippets: Unicode - Unicode on Oracle[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-13406.html">SQL Snippets: Unicode - Unicode on Oracle</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Unicode - Unicode on Oracle
  • URL (href): http://www.sqlsnippets.com/en/topic-13406.html