Oracle NVARCHAR2(4000) with AL32UTF8 possible?

Posted on

Question :

I thought, through reading items here and elsewhere, that if my Oracle database NLS_CHARACTERSET is set to AL32UTF8, I can have a column of type NVARCHAR2(4000), but when I try to create it, I get SQL Error: ORA-00910: specified length too long for its datatype. What is wrong with what I am trying? Are NVARCHAR2 columns always UTF16, unlike what I have read?

Answer :

If your NLS_CHARACTERSET is AL32UTF8, any VARCHAR2 column will support Unicode (via the UTF-8 encoding). In 99.9% of cases, you’d never want to create an NVARCHAR2 column.

The NVARCHAR2 column, which uses the NLS_NCHAR_CHARACTERSET which will almost always be AL16UTF16 is really only useful in a couple of cases. The first, and by far most common, is when you have a legacy application whose NLS_CHARACTERSET doesn’t support Unicode and cannot be migrated to Unicode but where you want to support Unicode data in a handful of columns. That’s not the case here. The second would be when the database does support Unicode but you want to use the UTF-16 encoding for some reason such as the ability to store primarily Japanese and Chinese data using mostly 2 bytes per character in UTF-16 rather than 3 bytes per character in UTF-8. But if you do that, you’re still (barring 12.1 and the ability to have 32k VARCHAR2 columns) limited to 4000 bytes (2000 characters in a UTF-16 column). Hence the error when you try to define NVARCHAR2(4000) which would need at least 8000 bytes to store 4000 characters.

Leave a Reply

Your email address will not be published. Required fields are marked *