Skip to Main Content
  • Questions

  • 10 scale digits getting rounded on 8th digit as number data type

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Tom.

Asked: February 19, 2016 - 6:47 pm UTC

Last updated: February 25, 2016 - 3:46 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Need to store 2457431456.6443981482 with 10 scale digits but no matter what precision and scale I use when retrieving number oracle rounds before and up to the 8th digit. Even when define as number(*,18) this does not help. I can retrieve entire 10 digit scale if use to_char but we need it sent back to application as actual number datatype NOT a character, otherwise would actually store as varchar2. Is there a setting to actually pull field from table as numeric datatype that will display original number inserted WITHOUT conversion to TO_CHAR?

and Connor said...

This will be a client display issue, not a database issue. The data definitely is being stored correctly...eg

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t (x number(25,10));

Table created.

SQL>
SQL> insert into t values (2457431456.6443981482);

1 row created.

SQL>
SQL> select * from t;

         X
----------
2457431457

SQL>
SQL> col x format 999999999999.999999999999
SQL>
SQL> select * from t;

                         X
--------------------------
   2457431456.644398148200


Rating

  (2 ratings)

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

Comments

Already knew answer, but part of question was ignored

Tom Hoskins, February 23, 2016 - 3:40 pm UTC

Maybe I didn't word question right, but part of question was most vital was not answered, I already knew answer given, below is part of question really needed answered:

" but we need it sent back to application as actual number datatype"

Displaying it fully in sqlplus is known and does not help with the above, we need to return entire number via a view as numeric to the calling application, which is .net based. displaying in sqlplus does not help in this situation. I do appreciate the help.
Chris Saxon
February 24, 2016 - 12:56 am UTC

My point is - SQLPlus as a client, demonstrates that when we send a number back to a client, we are indeed sending the entire number. Probably would have been better if I showed the value being retrieved into a client side data type, eg

SQL> variable n number
SQL> exec select x into :n from t;

PL/SQL procedure successfully completed.

SQL> col n format 999999999999.999999999999
SQL> print n

                         N
--------------------------
   2457431456.644398148200


So, what matters is what your *client* can accomodate. So if you fetched it into (say) a double, then you'd be limited to 15-16 significant digits and so forth.



Thanks

Tom Hoskins, February 24, 2016 - 4:15 pm UTC

Thanks for help, reality it Oracle as powerful as it is does not handle passing value that large as an actual number back to an application (client excluding sqlplus as you mentioned). Developers complain having to except as string from something so powerful as oracle should be able to present such large number that it stores 38 digits to allow presentation as 38 digits as well in a numeric datatype without having to convert to char
Chris Saxon
February 25, 2016 - 3:46 am UTC

Sorry - I'm not sure I follow what you are saying.

To my knowledge, there is Oracle client that cannot receive the full precision of numbers coming back from the database.

Can you elaborate more ?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.