Skip to Main Content
  • Questions
  • Unexpected results with NUMBER(32,16) datatype

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, BALAJI.

Asked: May 02, 2017 - 5:09 pm UTC

Last updated: May 10, 2017 - 1:20 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi

Number(32,16) datatype columns are behaving differently, When we update those columns, oracle automatically rounding the values to something else.

For eg. when we try to update the existing value from 1.0690000000000000 to 1.0690000000001234, oracle automatically changes this value 1.0690000000001198

This issue is happening after we upgrade the database to 12c


Please advice.


Thanks
Balaji N

with LiveSQL Test Case:

and Connor said...

Sorry - I cant see that behaviour (my test case below), and your livesql test case looks fine as well (ie, did not modify the value)

SQL> create table t ( x number(32,16));

Table created.

SQL> insert into t values (1.0690000000000000);

1 row created.

SQL> select * from t;

                          X
---------------------------
         1.0690000000000000

1 row selected.

SQL> update t set x = 1.0690000000001234;

1 row updated.

SQL> select * from t;

                          X
---------------------------
         1.0690000000001234

1 row selected.


Can you reproduce with a test case similar to what I've got above ?

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

BALAJI NADARAJAN, May 03, 2017 - 9:10 am UTC

Sorry, The issue is specific to my environment. As suggested please see the following

Connected to Oracle Database 12c Standard Edition Release 12.1.0.2.0

SQL> create table t ( x number(32,16));
Table created

SQL> insert into t values (1.0690000000000000);
1 row inserted

SQL> select * from t;
X
----------------------------------
1.0690000000000000

SQL> update t set x = 1.0690000000001234;
1 row updated

SQL> select * from t;
X
----------------------------------
1.0690000000001198

SQL> commit;
Commit complete

SQL> select * from t;
X
----------------------------------
1.0690000000001198

Thanks
Balaji N
Connor McDonald
May 04, 2017 - 2:00 am UTC

Well - you've got a very simple test case there. I think you'll need to log that with Support. Seems very odd.

BALAJI NADARAJAN, May 05, 2017 - 9:27 am UTC

Thank you for the response, It seems the issue is with PL/SQL developer tool and not with oracle DB, if I run the same test case on Unix server login as sysdba, Got the expected result. Please advice how to resolve this issue from pl/sql developer

Thanks
Balaji N

Connor McDonald
May 09, 2017 - 2:18 am UTC

Is this *our* product "SQL Developer" or is this the third party product "PL/SQL Developer" ?

BALAJI NADARAJAN, May 09, 2017 - 9:12 am UTC

Yes, it is the third party product "PL/SQL Developer"?
Connor McDonald
May 10, 2017 - 1:20 am UTC

Well... you need to speak to them about it.