SEARCH


 


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

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