ORA-00910: specified length too long for its datatype

Question

I have a column in Oracle to store comments of Nvarchar2(2000). When a user tries to enter beyond 2000 characters, I get the following error:

ORA-00910: specified length too long for its datatype.

The NLS_NCHAR_CHARACTERSET parameter is having AL16UTF16 value.

Is there any way to increase the size to accept up to 6000 characters? My column already has lots of contents, so not sure if I will be able to change the datatype from NVarchar(2000) to any other.

Answer 1

Unless you use Oracle 12c, it's not possible to store more than 2000 characters, see the datatypes description here:

http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm

Instead, you should use the NCLOB datatype.

If you use 12c, check: http://dbasolved.com/2013/06/26/change-varchar2-to-32k-12c-edition/