"ORA-01438: value larger than specified precision allowed for this column" when inserting 3

Question

I'm running into that error when trying to insert any number except 0 into a field with format NUMBER (2,2).

UPDATE     PROG_OWN.PROG_TPORCENTAJE_MERMA SET     PCT_MERMA = 3 WHERE     IDN_PORCENTAJE_MERMA = 1 

[Error Code: 1438, SQL State: 22003] ORA-01438: value larger than specified precision allowed for this column

COLUMN_NAME DATA_TYPE   TYPE_NAME   COLUMN_SIZE   BUFFER_LENGTH   DECIMAL_DIGITS PCT_MERMA   3           NUMBER      2             0               2 

It also happens if I try with decimal numbers.

Any idea why?

Answer 1

You can't update with a number greater than 1 for datatype number(2,2) is because, the first parameter is the total number of digits in the number and the second one (.i.e 2 here) is the number of digits in decimal part. I guess you can insert or update data < 1. i.e. 0.12, 0.95 etc.

Please check NUMBER DATATYPE in NUMBER Datatype.

Answer 2

NUMBER (precision, scale) means precision number of total digits, of which scale digits are right of the decimal point.

NUMBER(2,2) in other words means a number with 2 digits, both of which are decimals. You may mean to use NUMBER(4,2) to get 4 digits, of which 2 are decimals. Currently you can just insert values with a zero integer part.

More info at the Oracle docs.