Learn the cause and how to resolve the ORA-01438 error message in Oracle.
When you encounter an ORA-01438 error, the following error message will appear:
- ORA-01438: value larger than specified precision allows for this column
Cause of Error
You tried to assign a numeric value to a column, but the value was larger than the column will allow. This occurred during either an INSERT or an UPDATE statement.
How to Resolve the Error
The option(s) to resolve this Oracle error are:
Assign a smaller precision value to the column.
Modify the definition of the table to allow for a higher precision number in the column. This can be done with a ALTER TABLE statement.
For example, if you had a table 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 could correct the error with either of the following solutions:Solution #1
You can correct the INSERT statement to assign a smaller precision value to the supplier_id column as follows:
INSERT into suppliers (supplier_id, supplier_name) VALUES (12345, 'IBM');Solution #2
You can modify the table definition of the suppliers table to allow for a 6 digit precision number.
ALTER TABLE suppliers MODIFY supplier_id number(6);