Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: rounding behavior

    I'm getting a strange rounding effect, that I don't remember seeing in the past...

    Code:
    SQL> create table test_rounding
      2  (f1 number(17,15);
     
    Table created.
     
    SQL> insert into test_rounding 
    SQL>   values (14.699999999999999);
     
    1 row created.
     
    SQL> select * from test_rounding;
     
            F1         
    ---------- 
          14.7
    So, what is the source of this rounding? I get the same effect in TOAD and SQL*Plus, but OracleForms is displaying the actual value. It looks like the cutoff for roundingis having more than 9 values to the right of the decimal. Why would it do that? Any how does OracleForms know that there's more to this value?

    Code:
    SQL> create table test_rounding (f number);
     
    Table created.
     
     
    SQL> insert into test_rounding values (1.1);
    SQL> insert into test_rounding values (1.12);
    SQL> insert into test_rounding values (1.123);
    SQL> insert into test_rounding values (1.1234);
    SQL> insert into test_rounding values (1.12345);
    SQL> insert into test_rounding values (1.123456);
    SQL> insert into test_rounding values (1.1234567);
    SQL> insert into test_rounding values (1.12345678);
    SQL> insert into test_rounding values (1.123456789);
    SQL> insert into test_rounding values (1.1234567890);
    SQL> insert into test_rounding values (1.12345678901);
    SQL> insert into test_rounding values (1.123456789012);
    SQL> insert into test_rounding values (1.1234567890123);
    SQL> insert into test_rounding values (1.12345678901234);
    SQL> insert into test_rounding values (1.123456789012345);
    SQL> insert into test_rounding values (1.1234567890123456);
    SQL> insert into test_rounding values (1.12345678901234567);
    SQL> insert into test_rounding values (1.123456789012345678);
    SQL> insert into test_rounding values (1.1234567890123456789);
    SQL> insert into test_rounding values (1.12345678901234567890);
     
    SQL> select F1 from test_rounding;
     
            F1
    ----------
           1.1
          1.12
         1.123
        1.1234
       1.12345
      1.123456
     1.1234567
    1.12345678
    1.12345679
    1.12345679
    1.12345679
     
            F1
    ----------
    1.12345679
    1.12345679
    1.12345679
    1.12345679
    1.12345679
    1.12345679
    1.12345679
    1.12345679
    1.12345679
     
    20 rows selected.
    The rounding holds when you transfer a value with greater precision into a value with less. With SQL you don't see the actual value, although with PL/SQL, you do.

    Code:
    SQL> drop table test_rounding;
    drop table test_rounding
               *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL> create table test_rounding
      2  (f1 number(17,15), 
      3   f2 number(4,2)
      4  );
    
    Table created.
    
    SQL> insert into test_rounding (f1) values (19.999999999999999);
    
    1 row created.
    
    SQL> select * from test_rounding;
    
            F1         F2
    ---------- ----------
            20
    
    SQL> update test_rounding set f2 = f1;
    
    1 row updated.
    
    SQL> select * from test_rounding;
    
            F1         F2
    ---------- ----------
            20         20
    
    SQL> begin
      2    for row in (select f1, f2 from test_rounding)
      3    loop
      4      dbms_output.put_line (row.f1||' '||row.f2);
      5    end loop;
      6  end;
      7  /
    
    PL/SQL procedure successfully completed.
    
    19.999999999999999 20
    Is there a list of rules somewhere, which controls this behavior?
    Thanks,
    --=Chuck
    Oracle 10g (10.1.0.4)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It's a SQL Plus (or Toad) default formatting behaviour:
    Code:
    SQL> select 14.699999999999999 v from dual;
    
             V
    ----------
          14.7
    
    SQL> column v format 99.999999999999999
    SQL> select 14.699999999999999 v from dual;
    
                      V
    -------------------
     14.699999999999999

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Looks like we need to be more careful. Maybe it's just that we've never had any data with precision beyond 2 or 3 digits ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •