Description

jgebal

opened on Dec 4, 2017

Issue body actions

@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).

Activity

1. jgebal commented on Dec 4, 2017

   

   jgebal

   on Dec 4, 2017

   MemberAuthor

   More actions

   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
2. pesse commented on Dec 4, 2017

   

   pesse

   on Dec 4, 2017

   Member

   More actions

   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?
3. jgebal commented on Dec 4, 2017

   

   jgebal

   on Dec 4, 2017

   MemberAuthor

   More actions

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

   self-assigned this

   on Dec 4, 2017
5. tkleiber commented on Dec 4, 2017

   

   tkleiber

   on Dec 4, 2017

   More actions

   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
6. tkleiber commented on Dec 4, 2017

   

   tkleiber

   on Dec 4, 2017

   More actions

   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
7. pesse commented on Dec 4, 2017

   

   pesse

   on Dec 4, 2017

   Member

   More actions

   Thanks for the testcase, will investigate
8. jgebal commented on Dec 4, 2017

   

   jgebal

   on Dec 4, 2017

   MemberAuthor

   More actions

   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?
9. tkleiber commented on Dec 4, 2017

   

   tkleiber

   on Dec 4, 2017

   More actions

   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.
10. pesse commented on Jan 14, 2018

    

    pesse

    on Jan 14, 2018

    Member

    More actions

    @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
11. tkleiber commented on Jan 14, 2018

    

    tkleiber

    on Jan 14, 2018

    More actions

    I have seen this on SLES 11, this is a 64bit linux.
12. pesse commented on Jan 14, 2018

    

    pesse

    on Jan 14, 2018

    Member

    More actions

    @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 ?
13. jgebal commented on Jan 15, 2018

    

    jgebal

    on Jan 15, 2018

    MemberAuthor

    More actions

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

    added a commit that references this issue on Jan 15, 2018

    Added locale initialization from environment variables LC_ALL or LANG

    ba4b5b9
15. pesse

    closed this as completedin #59on Jan 16, 2018
16. pesse

    added a commit that references this issue on Jan 16, 2018

    Merge pull request #59 from utPLSQL/bugfix/nls_from_environment

    This issue will close once commit 5a54402 is merged into the 'develop' branch.

    5a54402

Issue actions