ORA-12899: value too large for column

Question

I am getting data from erp systems in the form of feeds ,in particular one column length in feed is 15 only.

In target table also corresponded column also length is varchar2(15) but when I am trying to load same into db it showing error like:

ORA-12899: value too large for column emp_name (actual: 16, maximum: 15)

I cant increase the column length since it is base table in the production.

Answer 1

have a look into this blog, the problem resolved for me by changing the column datatype from varchar(100) to varchar(100 char). in my case the data contains some umlaut characters.

http://gerardnico.com/wiki/database/oracle/byte_or_character

Answer 2

The usual reason for problems like this are non-ASCII characters that can be represented with one byte in the original database but require two (or more) bytes in the target database (due to different NLS settings).

To ensure your target column is large enough for 15 characters, you can modify it:

  ALTER TABLE table_name MODIFY column_name VARCHAR2(15 CHAR)

(note the 15 CHAR - you can also use BYTE; if neither is present, the database uses the NLS_LENGTH_SEMANTICS setting as a default).

To check which values are larger than 15 bytes, you can

• create a staging table in the target database with the column length set to 15 CHAR
• insert the data from the source table into the staging table

• find the offending rows with

  SELECT * FROM staging WHERE lengthb(mycol) > 15

(note the use of LENGTHB as apposed to LENGTH - the former returns the length in bytes, whereas the latter returns the length in characters)

Answer 3

I found AL32UTF8 as the only valid setting. This varies from standard UTF8 with a few character having supplementary bytes, i.e, the characters are about 99% the same. I am guessing you have character conversion problems going on. In other words the data in table1 was written using one charset, and the new table has a slightly different charset.

If this is true, you have to find the source of the oddball charset. Because this will continue to happen.

Answer 4

Solution to:

ORA-12899: VALUE TOO LARGE FOR COLUMN(ACTUAL,MAXIMUM)

If you are facing problem while updating a column size of a table which already has data more than the new length below is the simple script that would work definitely.

ALTER TABLE TABLE_NAME ADD (NEW_COLUMN_NAME DATATYPE(DATASIZE));
UPDATE TABLE_NAME SET NEW_COLUMN_NAME  = SUBSTR(OLD_COLUMN_NAME , 1, NEW_LENGTH);
ALTER TABLE TABLE_NAME DROP COLUMN OLD_COLUMN_NAME ;
ALTER TABLE TABLE_NAME RENAME COLUMN NEW_COLUMN_NAME  TO OLD_COLUMN_NAME;

Meaning of the query:

ALTER TABLE TABLE_NAME ADD (NEW_COLUMN_NAME DATATYPE(DATASIZE));

It would just create a new column of the required new length in your existing table.

UPDATE TABLE_NAME SET NEW_COLUMN_NAME  = SUBSTR(OLD_COLUMN_NAME , 1, NEW_LENGTH);

It will discard all the values after the new length value from old column values and set the trimmed values into the new column name.

ALTER TABLE TABLE_NAME DROP COLUMN OLD_COLUMN_NAME ;

It will remove the old column name as its absurd now and we have copied all the information into the new column.

ALTER TABLE TABLE_NAME RENAME COLUMN NEW_COLUMN_NAME  TO OLD_COLUMN_NAME;

Renaming the new column name to the old column name would help you regain the original table structure except for the new column size as you wished.