Ora-12899 Value Too Large For Column Oracle

Ora-12899 Value Too Large For Column Oracle | In this post we will learn how to resolve the Ora-12899 error:- value too large for column Oracle. ORA-1299 error represents the instance of either an uptick in data or a user who is forcing Oracle to stall during its requested action.

ORA-12899 is an Oracle error that occurs when the value entered into the string is too large. It happens when the user tries to update or insert a column with a value too large/wide for the destination column.

Cause of Ora-12899 Value Too Large For Column Oracle

When a user makes an attempt to insert or update a column with a value that is too large for the particular column will be given with the actual width of the value and also the maximum width permitted for the column. It occurs when we try to push through a value or set of values that is more than the specified maximum width of the column.

Solution for Ora-12899 Value Too Large For Column Oracle

Let’s see an example that corrects ORA- 12899, If the user has created the following table:-

CREATE TABLE students 
(
  id VARCHAR2(9) PRIMARY KEY,
  contact VARCHAR2(40) NOT NULL,
  address VARCHAR(15) NOT NULL
); 

Now the user attempts to Insert some value into the table using the insert statement which is shown below:-

INSERT INTO students VALUES 
(5251, 'John', '1600 Amphitheatre Parkway in Mountain View, California');

The user might try to run this statement, but he/she will be receiving this error message. Error for this might be:-

ERROR at line 1:
ORA-12899: value too large for column “KP”.”STUDENTS”.”ADDRESS” (actual: 54,
maximum: 15)

This error statement represents that the address variable can’t hold more than 15 characters that would exceed the length of the column parameter. The address value we have given (‘1600 Amphitheatre Parkway in Mountain View, California’) makes out the total number of characters (54) exceeds the maximum number allowed for the length of the column address.

To solve this problem, we could change the column length of VARCHAR2 for address to an amount that can accommodate the typical address length that the student would input.

ALTER TABLE students MODIFY address VARCHAR2(100);

Now, we have changed the size of the address column and we can insert the previous value in the address column.

INSERT INTO students VALUES 
(5251, 'John', '1600 Amphitheatre Parkway in Mountain View, California');

 SELECT * from students;

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!