If you live in English Canada and you use JDBC and oracle alot, you might have seen a mysterious java.lang.NumberFormatException. I've seen it before but only understood the root cause recently.
1) it all starts with poor geography knowledge of Americans or you can blame the bilingual system gets people confused. Once oracle knows your territory setting is Canada, oracle uses ‘,’ , rather than '.', as the seperator of decimal decimal(which internally decided by NLS_NUMBERIC_CHARACTERS nls parm), regardless you language setting is English or French. Thus when you are in English Canada, if your code convert a number into string in Oracle and return the string to java, then when java tries to read this string as a number, you'll get java.lang.NumberFormatException because java's language knowledge is better than oracle( because its inventor is a canadian?) and it expects '.' as decimial seperator in English Canada. This isn’t a new bug, it has been there since at least 8i and still the same in 10g R2. It seems Oracle doesnt' consider it is a bug. Oracle's argument is territory alone dictates NLS_NUMBERIC_CHARACTERS, not territory and language. But to me it definitely is a bug, it isn't even political correct.
2) How does Oracle gets to know the territory? In clients other than JDBC, such as sqlplus, it’ll read NLS_LANG in environment variable. If NLS_LANG doesn’t exist, it doesn’t bother to read locale, it’ll just set language to American and territory to America.
3) In JDBC, it is a different story. Maybe because JDBC has its own standard or Oracle eventually realized that setting nls to American_america regardless of locale setting isn't nice, Oracle changes its nls strategy. According to documentation, for JDBC OCI driver, in 9i, it has locale setting to overwrite NLS_LANG, if the latter exists. In 10g, the document says if nls_lang has language setting as “AMERICAN”, it won’t read locale, it will use whatever NLS_LANG says. This is where documentation doesn’t apply to the reality. From my test, in 10g, in windows, the NLS_LANG takes precedence over locale, which is what document says since we use American in nls_lang. In unix, it is the other way around.
4) The simplest solution to this problem is to set locale to english US. If you don't want to give up your canadian pride and you are lucky enough to have control of oracle connection, you can set NLS_NUMBERIC_CHARACTERS to '.,' when NLS_TERRITORY is canada and NLS_LANGUAGE is english on the session level once the connection is established. Change sql to force use '.' as seperator isn't good, what if your application one day is required to install on machine with locale setting as French?
