sql - "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.