NLS_LANG, JDBC , English Canada and java.lang.NumberFormatException

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?

datapump to dump data

I created an external table with type as ora_datapump, then used CTAS to extract a table with 8.7 million rows on a sunfire v490, it took 25 seconds, the file is about 1G in size. This is really fast, Let's put this into perspective:

1) A CTAS to create a real table from same source table took 40 seconds.
2) A direct export took 6 minutes.
3) A conventional export tool 10 minutes.
4) My own data extraction utility took 10 minutes.

The export dump file has about 1.3G bytes, which is 30% bigger than the datapump one. Look inside the file, datapump saves space in the places such as using "FF" to represent null, whilst in export dump, "FEFF" is used. And also for each column, datapump use 1 byte, instead of 2 bytes as in export dump, to represent the size. Reducing size might help the performace datapump, but this won't explain how come it is more than 10 times faster than direct export. Seems I should never use export in 10g.

Unfortunately, datapump can't dump data into plain text. It wouldn't be difficult to interpret the format and to write a conversion utility, the challenge would be how to make the conversion as fast as datapump itself. I actually wrote one in java, it ran 7 minutes in a sun machine. A bit slow...