Oracle Insert Into NVarchar2(4000) does not allow 4000 characters?
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;
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
).