NLS_LANG=german_switzerland

There is a bug about NLS_LANG and SWITZERLAND in 10g.

This is because the tausend separator has changed from 9i (.) to 10g (').

Check Note 4598613.8

The workaround is to not use NLS_LANG=german_switzerland, but if you have different NLS parameters on the client and on the server, than you will have other problems, ex: questionable statistics by exp/imp.

OK, here is the bug :


$ export NLS_LANG=german_switzerland.we8iso8859p1
$ sqlplus -L scott/tiger

SQL*Plus: Release 10.2.0.2.0 - Production on Do Sep 21 13:50:05 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

ERROR:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-02248: Ungültige Option für ALTER SESSION


SP2-0751: Anmeldung bei Oracle nicht möglich. SQL*Plus wird beendet


and my workaround


$ export NLS_LANG="" NLS_TERRITORY=switzerland NLS_LANGUAGE=german
$ sqlplus -L scott/tiger
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Sep 21 13:51:30 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> select * from NLS_SESSION_PARAMETERS ;
PARAMETER VALUE
---------------------- -----------
NLS_LANGUAGE GERMAN
NLS_TERRITORY SWITZERLAND
NLS_CURRENCY SFr.
NLS_ISO_CURRENCY SWITZERLAND
NLS_NUMERIC_CHARACTERS .'
NLS_CALENDAR GREGORIAN
NLS_DATE_LANGUAGE GERMAN
NLS_SORT GERMAN
NLS_DUAL_CURRENCY SF

17 Zeilen ausgewahlt.

SQL> select to_char(1000,'9G999') from dual;

TO_CHA
------
1'000

SQL> quit
Verbindung zu Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options beendet




So it is possible to use german_switzerland. Not sure if this workaround is bullet-proof. Metalink says it is fixed in 11g