How to resolve ORA-01438 Error Message in Oracle database

Learn the reason and how to get to the bottom of the ORA-01438 error message in Oracle.

Description

When you come across an ORA-01438 error, the following error message will appear:

ORA-01438: value large than specific precision approves for this column

Cause

You tried to assign a numeric fee to a column, however the cost used to be larger than the column will allow. This passed off all through either an INSERT or an UPDATE statement.

Resolution

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

Option #1

Assign a smaller precision value to the column.

Option #2

Modify the definition of the table to allow for a greater precision number in the column. This can be carried out with a ALTER TABLE statement.

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

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

And you tried to execute the following INSERT statement:

INSERT into suppliers
(supplier_id, supplier_name)
VALUES (123456, 'IBM');

You would receive the following error message:

You ought to right the error with either of the following solutions:

Solution #1

You can right the INSERT announcement to assign a smaller precision price to the supplier_id column as follows:

INSERT into suppliers
(supplier_id, supplier_name)
VALUES (12345, 'IBM');

Solution #2

You can regulate the desk definition of the suppliers desk to permit for a 6 digit precision number.

ALTER TABLE suppliers
 MODIFY supplier_id number(6);