How to resolve ORA-01401 Error Message in Oracle database

Isaac
Posted by By Isaac 1 year Ago 2 Min Read

Learn the motive and how to unravel the ORA-01401 error message in Oracle.

Description

When you come upon an ORA-01401 error, the following error message will appear:

ORA-01401: inserted value too large for column

Cause

You tried to insert a fee into a column that exceeds the most width for the column.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Correct your SQL to truncate the fee so that it suits within the field. You can always attempt the SUBSTR feature to truncate the value.

Option #2

Modify your table definition to allow for a large cost in a field. This can be achieved with a ALTER TABLE command.

For example, if you had a desk called suppliers described as follows:

CREATE TABLE suppliers
( supplier_id number not null,
  supplier_name varchar2(10)
);

And you tried to execute the following INSERT statement:

INSERT INTO suppliers
( supplier_id, supplier_name )
VALUES
( 10023, 'Hewlett Packard' );

You would receive the following error message:

You have defined the supplier_name column as a varchar2 that can only handle up to 10 characters. Yet, you have attempted to insert the price ‘Hewlett Packard’ (which is 15 characters in length) into this field.