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 
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
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
[edit]fixed in 10.2.0.3 and later [/edit]

Published by

Laurent Schneider

Oracle Certified Master

5 thoughts on “NLS_LANG=german_switzerland”

  1. another workaround is to set NLS_NUMERIC_CHARACTERS to ,.
    which is the way it was set in Oracle 9i

    NLS_LANG=german_switzerland.[whatever you want]
    NLS_NUMERIC_CHARACTERS=.,

  2. Pingback: Laurent Schneider » Blog Archive » 11g product availability

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>