ORA-01438: value larger than specified precision

ORA-01438: value larger than specified precision allowed for this column

A number has too many digits for its NUMBER(p,s) column. Remember that the digits before the decimal point are limited to p minus s. Fix the value or widen the column.

What ORA-01438 means

NUMBER(p,s) stores at most p significant digits, s of them after the decimal point. So NUMBER(5,2) holds values up to 999.99, not 99999. Digits after the point are rounded silently, but too many digits before it raise ORA-01438. The message does not name the column.

Common causes of ORA-01438

A value larger than the column allows, for example 12345 in NUMBER(3).
Underestimating the effect of scale: NUMBER(5,2) leaves only three digits before the point.
A wrong unit, such as cents instead of currency units, or a corrupted input.
A sequence or counter that has outgrown the column.

How to fix ORA-01438

Find the column by checking the precision and scale of the columns in the statement: USER_TAB_COLUMNS.
Validate the incoming value; a surprising number is often the real bug.
If the value is right, widen the column: ALTER TABLE t MODIFY (amount NUMBER(12,2)).

ORA-01438 example

NUMBER(3) holds at most three digits:

-- ORA-01438: value larger than specified precision allowed for this column
UPDATE products SET stock = 12345 WHERE id = 1;   -- stock NUMBER(3)

ALTER TABLE products MODIFY (stock NUMBER(7));

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-01438. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.