Number field displays differently on two systems.

[es336td]

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?

[BKBK]

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.

[es336td]

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]

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.

[es336td]

Understood... but why does it appear on one system as 123.87654321 and the other as 123,87700000?   The code and databases are identical except for the data source name.

[BKBK]

es336td wrote:

Understood... but why does it appear on one system as 123.87654321 and the other as 123,87700000?   The code and databases are identical except for the data source name.

I wouldn't know why. What I know is that 123.87654321 requires Number(11,8). In fact, if you expect the whole-number part of the data to have a maximum of, say, 9999, then you should set the type as Number(12,8).

[es336td]

I remove all designation and set to just NUMBER.   From Oracle documentation...

NUMBER(p,s)

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.

[BKBK]

es336td wrote:

I remove all designation and set to just NUMBER.   From Oracle documentation...

NUMBER(p,s)

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.

There are 2 different concepts of number here. You should not mix them up.

With Number, there is no explicit precision and no explicit scale. That is, there are no arguments p, s. In that case, Oracle stores a number in the form d * 10^s, where d is a number that can have up to 38 digits and s ranges between -84 and 127. This is similar to the so-called scientific notation.

However, your original question is about 123.87654321. You want this number to differ from 123.87700000. These are decimals, which imply you require precision and scale. The number 123.87654321 requires the type Number(11,8). You had allocated Number(10,8) to it, which falls short.

I also notice that the conversion 123,87700000 has a comma. If that is indeed so, and not just a typing mistake, then it means two things: (1) The conversion already occurred, even before the database, in a locale in which a comma is used as decimal separator; (2) the database column is not of numeric type.

[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.

[es336td · Accepted solution]

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...

[BKBK]

es336td wrote:

Mystery solved: the Gov't DBAs changed the wrong database.

It was always going to be something like that. It's nice to hear the issue is solved.