By default if you define something as NUMBER(3), the Oracle database will round the value, if possible to make the number fit, if the number cannot fit after rounding only then will it be rejected.

If you enforce the NUMBER(3) as a constraint and not a data definition we can get Oracle to reject the number before trying to fit it into our data type.

CREATE TABLE <tablename>
( X NUMBER
CHECK
(CAST (X AS NUMBER(3)) = x)
);

If you now try and insert a value with a decimal point it will be rejected:

insert into <tablename> values (123.1)
*
ERROR at line 1:
ORA-02290: check constraint (X.SYS_C003454) violated

How to return errors when converting floating point numbers into number types

Leave a Reply

Your email address will not be published. Required fields are marked *

− two = 7