Oracle Insert Into NVarchar2(4000) does not allow 4000 characters?

Question

I have a table with a field datatype of NVarchar2(4000) I am moving data from a SQL Server to an Oracle Server. The SQL Server datatype is also nvarchar(4000). I have checked the MAX Size of this field on the SQL Server side, and the MAX is 3996, which is 4 characters short of the 4000 limit.

When I try to insert this data into Oracle, I get an error "LONG" due to the size.

What is going on here, will the Oracle NVarchar2(4000) not allow 4000 characters? If not, what is the limit, or how can I get around this?

Answer 1

First, as others have pointed out, unless you're using 12.1, both varchar2 and nvarchar2 data types are limited in SQL to 4000 bytes. In PL/SQL, they're limited to 32767. In 12.1, you can increase the SQL limit to 32767 using the MAX_STRING_SIZE parameter.

Second, unless you are working with a legacy database that uses a non-Unicode character set that cannot be upgraded to use a Unicode character set, you would want to avoid nvarchar2 and nchar data types in Oracle. In SQL Server, you use nvarchar when you want to store Unicode data. In Oracle, the preference is to use varchar2 in a database whose character set supports Unicode (generally AL32UTF8) when you want to store Unicode data.

If you store Unicode data in an Oracle NVARCHAR2 column, the national character set will be used-- this is almost certainly AL16UTF16 which means that every character requires at least 2 bytes of storage. A NVARCHAR2(4000), therefore, probably can't store more than 2000 characters. If you use a VARCHAR2 column, on the other hand, you can use a variable width Unicode character set (AL32UTF8) in which case English characters generally require just 1 byte, most European characters require 2 bytes, and most Asian characters require 3 bytes (this is, of course, just a generalization). That is generally going to allow you to store substantially more data in a VARCHAR2 column.

If you do need to store more than 4000 bytes of data and you're using Oracle 11.2 or later, you'd have to use a LOB data type (CLOB or NCLOB).