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

There is a limit of 4000 bytes not 4000 characters. So NVARCHAR2(4000) with an AL16UTF16 national character set would occupy the maximum 4000 bytes.

From the oracle docs of MAX_STRING SIZE:

Tables with virtual columns will be updated with new data type metadata for virtual columns of VARCHAR2(4000), 4000-byte NVARCHAR2, or RAW(2000) type.

Solution:-

Also if you want to store 4000 characters then I would recommend you to use CLOB

A CLOB (Character Large Object) is an Oracle data type that can hold up to 4 GB of data. CLOBs are handy for storing text.

You may try like this to change column data type to CLOB:

ALTER TABLE table_name
 ADD (tmpcolumn  CLOB);

UPDATE table_name SET tmpcolumn =currentnvarcharcolumn;
COMMIT;

ALTER TABLE table_name DROP COLUMN currentnvarcharcolumn;

ALTER TABLE table_name
RENAME COLUMN tmpcolumn TO whatevernameyouwant;

Answer 2

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).

Answer 3

As per the documentation though the width refers to the number of characters there's still a 4,000 byte limit:

Width specifications of character data type NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes.

You probably have 4 multi-byte characters.