Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Decimal in Amount fields getting stored with comma in Oracle

 
AnushAmit Malhotra
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

The Decimal separator (.) in Amount fields in my Application are getting stored as comma (,) in Oracle 11g. This is for a European client. There was some discussion today on the NLS parameter & the way it is setup for Oracle Database.
As a simple example 99.99 is stored as 99,99 and while reading the value,application reads it as 9999.00.

Is there some way to handle this at the Oracle Driver Level or any other configuration level change as we do not want to make code changes.

Please let me know
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch!

It looks like something isn't right in your database or code. It's not just the NLS setting; NLS alone shouldn't cause the decimal separator to be completely ignored.

What datatype is the value stored in? IF you aren't sure, use the DESCRIBE command or some database tool to find out.

Also, we'll probably need to see the code that writes the value into the database and then reads it back. There's probably a mistake somewhere here, and we need to see it to be able to point it out. So, post the relevant code here, please.
 
AnushAmit Malhotra
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Martin.

The same code works correctly in our local environment and has undergone complete system testing. Column is defined as num(18,5)

Application gets data from a JMS message , this gets printed into application log files correctly i.e. 99.99 but at the table level, when we view this using SQL Developer, it shows off as 99,99.

When the application reads this data, it considers it as 9999.00 and the computing logic totally goes for a toss.

Any other pointers which I can check?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
AnushAmit Malhotra wrote:When the application reads this data, it considers it as 9999.00 and the computing logic totally goes for a toss.

Inspect carefully the code that reads the data from the database and prints it. That code is wrong. If you post it here, we can help you find the problem.

When a number is stored in the database, it isn't stored with the decimal separator. It's stored in an internal representation which is independent from regional settings, NLS or locale. When displayed by SQL Developer, the SQL Developer uses some settings (I believe it has its own settings) to format that number. When it is written to the log file, the decimal separator used depends on the locale used by the application. So it doesn't indicate any problem if it shows with different decimal separator in SQL Developer and in the log file.

Given that the number is shown up correctly in SQL Developer, the part of your application that writes that number is probably correct. So when the application reads and prints the data, it does it wrong. I can't guess exactly what it does, there are many ways to do something wrong, and it is possible that the bug only appears with some specific (eg. European) national language settings. But it is a bug nevertheless and needs to be tracked down.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic