Knowledge Base

Make sure the value inserted into the column is less than or equal to the allowed size specified for the column

ORA-01401: Inserted value too large for column

The value you are trying to insert into a column is too long.
eg:

SQL> create table test(n varchar2(1));Table created.SQL> insert into test values ('a');1 row created.SQL> insert into test values ('aa');insert into test values ('aa')                         *ERROR at line 1:ORA-01401: inserted value too large for column

It can happen that the character you are trying to insert looks like there should be no problem:

SQL> desc test Name                                      Null?    Type ----------------------------------------- -------- ----------- N                                                  VARCHAR2(1)SQL> insert into test values ('?');insert into test values ('?')                         *ERROR at line 1:ORA-01401: inserted value too large for column

This can happen due to the characterset the database is running in.

In a AL32UTF8 (unicode) database, some characters take more then 1 byte.

If you do not specify at create time what unit the size for a column is in, default it means the amount of bytes.
In our example, the ? will take 2 bytes due to the fact we are in a Unicode database and will cause the error.

SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL32UTF8

We can fix this by telling that the size is in CHAR’s instead of BYTE’s:

SQL> alter table test modify(n varchar2(1 char));
Table altered.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------
 N                                                  VARCHAR2(1 CHAR)

SQL> insert into test values ('?');
1 row created.
Oracle Gold Partner DbMotive is an Oracle Gold Partner