Insert fails for value 100 with column definition NUMBER(4,2)

Question

I have an Oracle table as follows.

create table test_table
(
  id number(19),
  width number(4,2)
);

When I try to insert width = 100 into this table, it fails to complain that value is larger than precision.

insert into test_table values (1, 100);

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

I read the documentation of Oracle on the definition of NUMBER. Based on that, it seems the INSERT should be correct.

Can someone explain what is wrong in above insert?

ArtBajji ArtBajji · Accepted Answer · 2018-03-30T03:51:10

As per OracleDocumentation when you declare a number datatype with precision p and scale s, p represents the number of digits the you can specify, in your case 4. As your scale is 2, the maximum number of digits before decimal is now restricted to 2. So in your case the maximum value is 99.99.

Insert fails for value 100 with column definition NUMBER(4,2)

2 Answers