SQL Error: ORA-12899: value too large for column

Question

I have created the following table

CREATE TABLE Customers(
  CustomerID varchar2(9) PRIMARY KEY, 
  Customer_Contact varchar2(40) NOT NULL, 
  Address varchar2(20) NOT NULL,
  Post_Code varchar2(7) NOT NULL, 
  Telephone_Number varchar2(11) NOT NULL)

And I am currently trying to use the INSERT VALUES statement. I have written the following statement

INSERT INTO Customers VALUES(
  501623129, 
  'John Petterson', 
  '-- Singleton Close London', 
  '--- ---', 02082860222)

When I try to run the statement it gives me the following error message.

Error starting at line 4 in command: INSERT INTO Customers VALUES(501623129, 'David Patterson', '30 Singleton Close London', 'SW17 9JY', 02082860642) Error report: SQL Error: ORA-12899: value too large for column "DJ"."CUSTOMERS"."ADDRESS" (actual: 25, maximum: 20) 12899. 00000 - "value too large for column %s (actual: %s, maximum: %s)"

Answer 1

ORA-12899: value too large for column "DJ"."CUSTOMERS"."ADDRESS" (actual: 25, maximum: 2

Tells you what the error is. Address can hold maximum of 20 characters, you are passing 25 characters.

Answer 2

Try this:

insert into CUSROMER_POST (CUS_NUM_BOX,CUS_NAE3_CUSTOMER,CUS_NAME,CUS_ID_OFFICE,CUS_DAET,CUS_MOLAHADA,CUS_PHON,CUS_EMIL,CUS_ADDRES) values (:P8_CUS_NUM_BOX,:P8_CUS_NAE3_CUSTOMER,:P8_CUS_NAME,:P8_CUS_ID_OFFICE,:P8_CUS_DAET,:P8_CUS_MOLAHADA,:P8_CUS_PHON,:P8_CUS_EMIL,:P8_CUS_ADDRES);

Answer 3

I just wanted to share here as the top result from Google...

I was trying to add virtual column like this

alter table t1 add (
    export_code varchar2(3 char) as (case when country is null then code else substr(code, 3) end) virtual
);

and oracle complained:

ORA-12899: value too large for column "EXPORT_CODE" (actual: 12, maximum: 480)

The problem is, that my code column is varchar2(120) so substr is up to 120 characters.

One can find, when creating a temporary table:

create table t2 as
    select case when country is null then code else substr(code, 3) end as export_code from t1;

and desc returns

Name        Null? Type          
----------- ----- ------------- 
EXPORT_CODE       VARCHAR2(120) 

once I realized, the solution is to use export_code varchar2(120 char) or do the cast:

alter table t1 add (
  export_code varchar2(3 char) as ( cast (case when country is null then code else substr(code, 3) end as varchar2(3 char) ) ) virtual
);

Answer 4

This answer still comes up high in the list for ORA-12899 and lot of non helpful comments above, even if they are old. The most helpful comment was #4 for any professional trying to find out why they are getting this when loading data.

Some characters are more than 1 byte in length, especially true on SQL Server. And what might fit in a varchar(20) in SQLServer won't fit into a similar varchar2(20) in Oracle.

I ran across this error yesterday with SSIS loading an Oracle database with the Attunity drivers and thought I would save folks some time.

Answer 5

As mentioned, the error message shows you the exact problem: you are passing 25 characters into a field set up to hold 20. You might also want to consider defining the columns a little more precisely. You can define whether the VARCHAR2 column will store a certain number of bytes or characters. You may encounter a problem in the future where you try to insert a multi byte character into the field, for example this is 5 characters in length but it won't fit into 5 bytes: 'ÀÈÌÕÛ'

Here is an example:

CREATE TABLE Customers(CustomerID  VARCHAR2(9 BYTE), ...

or

CREATE TABLE Customers(CustomerID  VARCHAR2(9 CHAR), ...

Answer 6

example : 1 and 2 table is available

1 table delete entry and select nor 2 table records and insert to no 1 table . when delete time no 1 table dont have second table records example emp id not available means this errors appeared

Answer 7

In my case I'm using C# OracleCommand with OracleParameter, and I set all the the parameters Size property to max length of each column, then the error solved.

OracleParameter parm1 = new OracleParameter();
param1.OracleDbType = OracleDbType.Varchar2;
param1.Value = "test1";
param1.Size = 8;

OracleParameter parm2 = new OracleParameter();
param2.OracleDbType = OracleDbType.Varchar2;
param2.Value = "test1";
param2.Size = 12;