Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Decimal in Amount fields getting stored with comma in Oracle

 
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
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • 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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • 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.
 
It looks like it's time for me to write you a reality check! Or maybe a tiny ad!
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic