Oracle NLS and Locale settings

[Norman Dignard]

I'm having a little dilimate understanding what the proper locale and NLS setting should be on our Oracle db. Were running HPUX 11i with Oracle 10GR2 which is the back end db for one of our apps. The propbelm we're seeing is that french characters are not being saved to the db yet you can go in via sqlplus and save/query them directly. Users connect to the db via Windows based tools or a Web broswer. We app's web server is another HPUX 11i box with no locale defined.

DB server HPUX 11i - no LANG or LC_ALL variables defined so defaults to C
ARS 7.01 p4 installed as root, non-unicode
Oracle 10.02.03 64bit
Oracle db â char set we8iso8859p1, national char set AL16UTF16
Oracle user NLS_LANG=american_america.we8iso8859p1

I'm planning on dumping the db and recreating them from scratch(I can't change the db char set due to errors) with the following
ARS 7.0.1 p4 with Unicode installed as non-root

Set Oracle NLS_LANG=american_america.utf8
Note: Iâ m questioning this. The db needs to support both English & French characters. User inputs (in either language) are via mid-tier using either French or English based forms.

min/user tool access from Windows is typically English. Should this var perhaps be fr_CA.iso8859p1, fr_CA.we8mswin1252 or something else?

- Create new db specing:
Char set AL32UTF8
National Char set AL16UTF16
Lang â American
Date - US
NLS_LENGTH_SEMANTICS=CHAR

Set LANG=en_US.utf8 in /etc/profile


I've attached a file provided by the ARS vendor which explains some of this but I would like a second opioning as to my setup ot ensure I did not overlook something.

[Eric Antunes]

Hi Norman,

Your APPS Character Set environment should equal to your DB Character Set environment: WE8ISO8859P1

Best Regards,

Eric

[Norman Dignard]

Due to the way the vendor app write to the db we set NLS_LANG and teh db char set to WE8MSWIN1252