ORA-01438 means a number has more digits before the decimal point than its NUMBER column allows. A column declared NUMBER(p, s) holds at most p digits in total, s of them after the decimal point, so p minus s digits before it. Digits beyond the scale after the point are rounded away silently, but digits that do not fit before the point raise this error.
Reproduce the Error
RATE is NUMBER(5,2): up to three digits before the point and two after.
Example:
create table demo_rates (code varchar2(3), rate number(5,2));
insert into demo_rates values ('EUR', 123.45);
insert into demo_rates values ('JPY', 1234.5);
insert into demo_rates values ('KWD', 0.305);
commit;
select code, rate from demo_rates;Output:
Table DEMO_RATES created.
1 row inserted.
Error starting at line : 3
In command -
insert into demo_rates values ('JPY', 1234.5)
Error at Command Line : 3 Column : 39
Error report -
SQL Error: ORA-01438: value 1234.5 greater than specified precision (5, 2) for column
1 row inserted.
Commit complete.
CODE RATE
_______ _________
EUR 123.45
KWD 0.31- 1234.5 has four digits before the point and is rejected. Oracle AI Database 26ai shows the value and the precision in the message; the column name after for column was blank in this test.
- 0.305 has three digits after the point and is rounded to 0.31 without an error.
Fix: Widen the Column, or Check the Data
Example:
select code, rate from demo_rates order by code;
alter table demo_rates modify rate number(9,4); -- more digits before and after the point
insert into demo_rates values ('JPY', 1234.5);
select code, rate from demo_rates order by code;Output:
CODE RATE _______ _________ EUR 123.45 KWD 0.31 Table DEMO_RATES altered. 1 row inserted. CODE RATE _______ _________ EUR 123.45 JPY 1234.5 KWD 0.31
NUMBER(9,4) allows five digits before the point and four after, enough for the yen rate and for more precise rates. Increasing precision and scale works on a filled column.
Things to Know
- A value that should never be that large is a data problem, such as amounts in cents loaded into a dollars column; fix the data rather than the column.
- Silent rounding beyond the scale can lose information; give the column enough scale for the data.
- For text columns that are too short, the error is ORA-12899.
Related Guides
Conclusion
ORA-01438 means a number has more digits before the decimal point than NUMBER(p, s) allows. Compare the value with p minus s, then widen the column or correct values that are too large to be right.
