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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL Error: ORA-12899: value too large for column

Examples related to sql

Examples related to oracle

Examples related to ora-12899