May 132013
create table fractional_digits_disappear (
  without_warning_when_put_in_an integer

insert into fractional_digits_disappear (
) values (

select *
from    fractional_digits_disappear;


The fractional seconds of timestamps are also silently lost when these are inserted into dates.
Not raising an error for this could be confusing, and potentially an "inexplicable" bug waiting to happen.

  8 Responses to “Where did all the decimals go?”

  1. Chris,

    thanks for reminding us on that !

    This one raises an exception

    create table long_strings_are_not_shortened( this_is_too_short varchar2( 23 ));
    insert into long_strings_are_not_shortened values('this raises an exception');
    ERROR at line 1:
    ORA-12899: value too large for column
    "DP"."LONG_STRINGS_ARE_NOT_SHORTENED"."THIS_IS_TOO_SHORT" (actual: 24, maximum: 23)

    not real consistent behaviour in my eyes


    • I agree, I’d prefer it if an exception was raised. I think the issue is greater in PL/SQL – if you manage to assign a decimal to a temporary variable which is an integer it can be very confusing when the fractional digits are lost! At least when the data is in a table you’re only expecting an integer at the end.

  2. The following check constraint can be used to raise an error if the number is not an integer:

    CONSTRAINT my_integer_ck CHECK ( my_integer = CAST( my_integer AS NUMBER(*,0) ) )

    • Interesting. You’d need to define your datatypes as numbers instead of integers, otherwise they still just get silently truncated though.

    • Kevan, this doesn’t work for me, which version are you on ?

      sokrates@11.2 > select * from v$version where rownum=1;

      Oracle Database 11g Enterprise Edition Release – 64bit Production

      sokrates@11.2 > create table fractional_digits_disappear (
      2 without_warning_when_put_in_an integer
      3 constraint my_integer_ck CHECK ( without_warning_when_put_in_an = CAST( without_warning_when_put_in_an AS NUMBER(*,0) ))
      4 );

      Table created.

      sokrates@11.2 > insert into fractional_digits_disappear (
      2 without_warning_when_put_in_an
      3 ) values (
      4 3.141592
      5 );

      1 row created.


      • got it

        sokrates@11.2 > alter table fractional_digits_disappear modify ( without_warning_when_put_in_an number );

        Table altered.

        sokrates@11.2 > insert into fractional_digits_disappear values ( 3.14 );
        insert into fractional_digits_disappear values ( 3.14 )
        ERROR at line 1:
        ORA-02290: check constraint (DP.MY_INTEGER_CK) violated

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>