NVARCHAR and NCHAR in oracle

Oracle supports multiple charactersets at the same time through its use of the NLS* paramters.

select *
from nls_database_parameters
where parameter in ('NLS_LENGTH_SEMANTICS',
		    'NLS_CHARACTERSET',
		    'NLS_NCHAR_CHARACTERSET');

There are two things to note

  1. The value of NLS_CHARACTERSET
  2. The value of NLS_NCHAR_CHARACTERSET

on my system the values are the following

NLS_NCHAR_CHARACTERSET = AL16UTF16

NLS_CHARACTERSET = WE8ISO8859P1

If you haven’t already guessed from the title of this article, NVARCHAR and NCHAR are two additional data types which behave similar to VARCHAR and CHAR. This means we could have a table with schema like the following

create table mytable
(
    A nvarchar(20),
    B varchar(20),
    C nchar(20),
    D char(20)
);

The difference is, while the characterset for varchar and char come from the value of NLS_CHARACTERSET , NVARCHAR and NCHAR derive their charactersets from the value of NLS_NCHAR_CHARACTERSET. Guess which one lets us use UTF?

This additional characterset is particularly useful when you have an oracle DB without unicode support but want to add unicode support.