Skip to main content
E
es336td
Inspiring
March 3, 2014
Answered

Number field displays differently on two systems.

  • March 3, 2014
  • 1 reply
  • 1020 views

I have a ColdFusion front end/Oracle backend application I inherited from the company that lost the contract to my company.  In one screen, there is a field the customer wished to increase from NUMBER (10,3) to NUMBER (10,8).  After researching and conferring with the Gov't DBAS supporting the production and test systems, it was suggested that I just alter the field to plain NUMBER,  I did so on my Development system and changed the CF code to allow for proper display.  It worked quite well.   I had the Gov't DBAs change the field in the production and test databases to match.   I then FTP'd the CF code to the test system.   It allows for entry and initial display of the new field size, but upon saving it (unlike my Development test) truncates/rounds up (i.e.- 123.87654321 becomes 123.87700000).  I have ensured the proper code is there and verified with the Gov't DBAs that the databases matches my Develop database.   What things could cause this problem?   It has me baffled.   All the instances are identical and the code on Development and Test is identical.   Any ideas?

This topic has been closed for replies.
Correct answer es336td

The customer wants 8 digits after the decimal, however that can be accomplished.   The Gov't DBAs I deal with ensured me that just changing the datatype to NUMBER would allow what I wanted without specifically entering 10,8 or 11,8, or whatever.   It works correctly on my development db.  It does not on the test.  I have control over the development, changed fhe field to NUMBER only and it correctly displays on my development application.  I copy the exact same code over to Test, after the DBAs changed the field to NUMBER only, and it does the round up thing, padding the last 5 with zeroes.  Yes, the comma was my fat fingering when posting.  My apologies.  The bottom line is, the customer wants to enter up to 8 digits after the decimal.  On my development db, they can.  On Test, they cannot.


Mystery solved: the Gov't DBAs changed the wrong database.  Once it was fixed, it worked.   I hate having to depend on someone else to maintain one of my databases.  Part of the job, though, so...

1 reply

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
March 4, 2014

es336td wrote:

... there is a field the customer wished to increase from NUMBER (10,3) to NUMBER (10,8)... upon saving it (unlike my Development test) truncates/rounds up (i.e.- 123.87654321 becomes 123.87700000).

The issue arises possibly because the number of digits exceeds the precision. In the definition Number(x,y), the precision x stands for the total number of digits, and the scale y stands for the number of digits after the decimal point. For your number 123.87654321, the total number of digits is 11, which exceeds the precision 10.

E
es336tdAuthor
Inspiring
March 4, 2014

Yes, I understand that.  Previously it was set to NUMBER (10,3) but the other DBAs changed it to just NUMBER  isn't that default (15,38)?

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
March 4, 2014

es336td wrote:

...the other DBAs changed it to just NUMBER 

isn't that default (15,38)?

(15,38) is likely a mistake. The precision must at least equal the scale. The Oracle documentation on numerical types suggests that the default precision and scale are (38,2).

In any case, in my previous posting I only wished to explain why the system would have saved 123.87654321 as 123.87700000. If you have 11 pigeons and 10 holes, then something's bound to give.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices