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

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?

This question is related to sql oracle

The answer is


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.