This content has been marked as final. Show 3 replies
Essentially, if the characterset of your database (NLS_CHARACTERSET from NLS_DATABASE_PARAMETERS) does not support the characters/encodings that you require then NLS_NCHAR_CHARACTERSET and NVARCHAR2 and NCLOB offer you an alternative.
Otherwise you're faced with rebuilding your database with the characterset you need, which imho is the best approach if possible.
The CHAR data types (VARCHAR2, CHAR, LONG, CLOB) store data in the database character set. The NCHAR data types (NVARCHAR2, NCHAR, NCLOB) store data in the national character set. The national character set can be either AL16UTF16 (default) or UTF8 (rare compatibility requirements). The database character set may be any of tens of character sets supported by Oracle. The recommended database character set is AL32UTF8.
Both AL16UTF16 and AL32UTF8 are Unicode encodings -- UTF-16BE and UTF-8, correspondingly.
The advantages of NCHAR data types:
- They are guaranteed Unicode data types, that is, any database since Oracle 9.0 can store Unicode data in NVARCHAR2, NCHAR, and NCLOB columns.
- Unicode storage of South and East Asian languages is more compact in AL16UTF16 compared to AL32UTF8. AL16UTF16 storage is possible only in NCHAR data types.
The (serious!) disadvantages of NCHAR data types:
- You need special coding in client access APIs to make sure that data you want to store in NCHAR data type columns does not go through conversion to the database character set, losing the "guaranteed Unicode" advantage.
- There are Oracle components that do not support NCHAR data types, most notably Oracle Text and XDB.
- It is confusing and error-prone to work with two database character sets, the database character set and the national character set.
- Storage of most European languages is more compact in AL32UTF8 compared to AL16UTF16.
- For any new database, create it with the AL32UTF8 character set and forget about NCHAR data types.
- For any existing application to be made multilingual, migrate the backend database to AL32UTF8 and forget about NCHAR data types.
- For any existing non-Unicode database serving a large legacy application system that is too costly or impossible to migrate to Unicode, to which you are asked to add a minor module that has to support multilingual data and for which a separate database makes little sense, you may consider NVARCHAR2 columns for this multilingual data.