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]
one of the workaround on metalink : “use a 10gR1 client” is not working.
well, actually it may work with a 10gR1 client, but you may need a patch if you have 10.1.0.5, and the patch is maybe not available on your platform…
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=.,
yes, but you will still get questionnable statistics when exporting…
Pingback: Laurent Schneider » Blog Archive » 11g product availability