National/regional settings not set correctly

[jgebal]

@tkleiber reported to em that, when running client on machines using different language, date&number format, they run into issues. Client seems to ignore local machine settings and this leads to their code running into some issues.

I hope to get more details on the type of issues they face and reasons for that.
It would be good to investigate the effort needed for the client to use local-machine language, date&number settings either from system (most preferable) or from NLS env parameters (if set).

[jgebal]

Some googling on this:
https://stackoverflow.com/questions/1079985/nls-lang-setting-for-jdbc-thin-driver
https://stackoverflow.com/questions/7983532/oci-jdbc-driver-and-nls-settings

[pesse]

NLS for country and language should be not too hard to get in - we'll have to adjust the bat/shell files though. Have to investigate if appassembler provides options for that kind of stuff.

Which other NLS env parameters would we want to use from environment?

[jgebal]

Language and region should be enough. Rest of the settings should be derived from those two I think.

[tkleiber]

Yes I for the moment I think that language and region should be mapped to NLS_LANG.

It seems that oracle has dropped the support for NLS variable with version 10 of the odbc drivers.

Before the jvm variable user.language (which derives from LANG or LC_ALL) would set NLS variables accordingly.

Further there was at least till 10g a InitializationString parameter in the connect string, which allows any sql commands to do such initialization (which would help us with another issue): https://docs.oracle.com/cd/E13222_01/wls/docs103/jdbc_drivers/oracle.html

[tkleiber]

Testcase, which fails on our (german) environment, as it does deliver a comma instead the point as decimal character:
ut.expect(to_char(24566698.76)).to_equal('24566698.76');

Have tried following before calling cli without success:
export LANG=en_US.utf-8
export LC_ALL=en_US.utf-8
export _JAVA_OPTIONS=-Duser.language=en

[pesse]

Thanks for the testcase, will investigate

[jgebal]

Hmmm.
Just wondering. Do you use a lot of to_char / to_date with default NLS format in your code?
Is this test case presented just to ensure pose the issue or are you actually using to_char in ut.expect?

[tkleiber]

Just to ensure pose the issue. A real world example is eg. invoices to customers in it's user defined preferences, which are not the session parameters and to ensure that session parameters does not influence the output.

[pesse]

@tkleiber can you tell me if you experience that problem on windows or linux? I investigated it and could reproduce the situation on windows. The main problem is that jvm seems to get the wrong locale, no matter if you set LC_ALL as environment variable or not (always gets the system locale which in my case is de_DE, too).
I can (an maybe will) introduce command line parameters to just override Locale, but I'd like to investigate the problem a bit deeper before

[tkleiber]

I have seen this on SLES 11, this is a 64bit linux.

[pesse]

@tkleiber I have no clues why JVM doesn't react to LC_ALL setting in your (and my case), but I managed to change the locale and therefore the database NLS settings by adding the following to environment:

export JAVA_TOOL_OPTIONS=-Duser.country=us -Duser.language=en

Can you verify this is working in your environment?
I could add a new commandline parameter "-locale=en_US.utf-8" of course, but this is a general JVM topic and I can imagine something like this would rather increase the confusion.
What's the opinion of the others, @jgebal , @viniciusam ?

[jgebal]

I would askTom. They definitely have it solved in SQLCL and SQLDeveloper.