Lokalisierungsproblem? bei TO_CHAR-Fkt.

[Jumpy]

Hallo,

die folgende Abfrage soll dazu dienen die Werte in einem Number-Feld, die entweder keine oder zwei Nachkommastellen haben, auf jeden Fall mit zwei Nachkommastellen auszugeben:

SQL-Code:

Select
TRIM(TO_CHAR(BETRAG_EU,999999999999990.99)) AS WERT
From
TABELLE

Nur kommen da bei zwei Rechnern unterschiedliche Ergebnisse raus (abgefragt wird natürlich die selbe DB). Ist der Wert in der DB z.B. 1234 so liefert Rechner1 1234.00 und Rechner2 12,34.

Rechner1: Win7, OracleClient 11g, Oracles-ODBC-Treiber
Rechner2: Windows Server 2008, OracleClient 11g, Oracles-ODBC-Treiber

Ich vermute, dass es irgendwie mit der Lokalisierung/Ländereinstellung der Rechner zu tun hat, doch versteh ich das nicht so ganz, da die eigentliche Operation doch auf der DB ausgeführt wird und an dern Rechnern als Antwort der DB doch nur eine Zeichenkette ankommen sollte?
Oder liegt das daran, das ODBC den Befehl anders übermittelt? Hab da in Google eine entsprechende Bemerkung zu gefunden, dies aber abgetan, da der ODBC-Treiber in beiden Fällen gleich eingestellt ist.

Wer hätte demnach eine Vermutung, was es sein könnte oder kennt einen anderen Weg, den Wert auf jeden Fall mit zwei Nachkommastellen zu erzwingen?

[DeddyH]

Win7, da war doch mal was. Stell doch auf dem entsprechenden Rechner einmal aus US um und dann wieder zurück auf DE. Zeigt Rechner1 dann auch einen falschen String? Im Übrigen könnte man sich auch überlegen, ob man den Wert nicht einfach nummerisch ermittelt und bei der Ausgabe erst formatiert.

[Jumpy]

Der Win 7 Rechner zeigt eigentlich das gewollte Verhalten, da bei uns (aus anderen Gründen) alles englisch lokalisiert ist.
Der Windows Server macht eher das für uns überraschende.
Hätte ich vllt. dabeisagen sollen ;-)

[DeddyH]

Ich bin jetzt nicht der große Oracle-Experte, aber Du könntest einmal auf beiden Rechnern NLS_LANG prüfen. Siehe auch http://www.datenbank-sql.de/nls.htm

[jobo]

Prüfe auf beiden Rechnern die NLS Session settings.
Wenn nicht innerhalb der Session extra geändert, gilt Registry gewinnt vor Server Einstellungen.

Was auch immer wo eingestellt ist, am Ende zählen die Session Settings:

Code:

select * from nls_session_parameters

ergibt z.B. sowas:

Code:

1   NLS_LANGUAGE   GERMAN
2   NLS_TERRITORY   GERMANY
3   NLS_CURRENCY   ¿
4   NLS_ISO_CURRENCY   GERMANY
5   NLS_NUMERIC_CHARACTERS   ,.
6   NLS_CALENDAR   GREGORIAN
7   NLS_DATE_FORMAT   DD.MM.RR
8   NLS_DATE_LANGUAGE   GERMAN
9   NLS_SORT   GERMAN
10   NLS_TIME_FORMAT   HH24:MI:SSXFF
11   NLS_TIMESTAMP_FORMAT   DD.MM.RR HH24:MI:SSXFF
12   NLS_TIME_TZ_FORMAT   HH24:MI:SSXFF TZR
13   NLS_TIMESTAMP_TZ_FORMAT   DD.MM.RR HH24:MI:SSXFF TZR
14   NLS_DUAL_CURRENCY   ¿
15   NLS_COMP   BINARY
16   NLS_LENGTH_SEMANTICS   BYTE
17   NLS_NCHAR_CONV_EXCP   FALSE

Hier dürfte Parameter NLS_NUMERIC_CHARACTERS für Dich interessant sein.

[Jumpy]

Da liegt der Hund begraben.
Auf dem Win7: NLS_Languaga=American und NLS_Numeric_Characters=.,
Auf dem WS2008: NLS_Languaga=Germen und NLS_Numeric_Characters=,.

Was sind den diese NLS Session Settings?
Ist das eine Einstellung bei der Installtaion des Oracle-Clients (uns wurde nämlich immer gesagt, dass wir dabei explizit nur englisch als Sprache wählen sollen und deutsch ja nicht), könnte also sein, das da einer bei der Installation was falsch gemacht hat.

Oder ist das eine Einstellung des Betriebssystems?

[DeddyH]

Am Besten kann das wohl der Hersteller erklären.

[Jumpy]

Jo, danke. Haben gerade mal in die Regitry geguckt. Der Oracle-Client wurde falsch installiert. Haben das in der Regitry jetzt geändert und es macht was es soll.



Sollte natürlich jemand geben, der generell eine bessere/elegantere Idee hat, wie man Nachkommastellen erzwingen kann: Immer her damit :-)

[DeddyH]

Clientseitig das DisplayFormat vorgeben?

[jobo]

Erzwingen kann man es zur Laufzeit, also innerhalb der Anwendung durch Absetzen von:

Code:

alter session set NLS_DATE_FORMAT = 'dd.mm.yyyy';

usw.

Wenn die Connection in der Anwendung geschlossen und geöffnet wird, muss man es wiederholen.
Wie gesagt, die NLS Settings können auf 3 Ebenen gesetzt werden. Server, Client[installation] (also Registry unter windows) und Session. Letzte Instanz ist die Session, die ist allerdings flüchtig. Alles was fehlt oder nicht angegeben ist, wird ausgehend von den Serversettings verwendet.

Das Displayformat im Client hilft bei der to_Char Funktion nicht, da es sich innerhalb von Oracle abspielt.

Nachtrag:
Die NLS Settings definieren alle sprachabhängigen Darstellungen, länderspezifisch. Wird auch (bei anderen Anbietern) als locale settings o.ä bezeichnet. Das hat mit dem OS überhaupt nichts zu tun, es ist komplett unabhängig, allerdings nimmt der Installer die Einstellungen des Wirtsystems als Vorgabe. Eine Installation des Servers auf einem deutschen Windows ergibt also andere NLS Settings als auf englischen Servern, ebenso beim Client.
Man kann es aber alles verstellen.

[DeddyH]

Falls ich das richtig verstanden habe, wird das TO_CHAR ja nur verwendet, um ein bestimmtes Ausgabeformat zu erreichen. Wenn man das aber clientseitig macht, kann man sich das sparen und einfach den Originalwert abfragen.

[jobo]

Klar, wenn man auf das to_char verzichtet, kann man es im Client erledigen. Das ist eine Geschmacksfrage oder abhängig vom Anwendungsfall.
Es schadet aber auch nicht, zu wissen, was die NLS Settings sind. Sie kommen z.B. bei jeder impliziten Typkonvertierung von Zahlen und Datumswerten ins Spiel und können böse weh tun, wenn man ihr Verhalten bzw. unterschiedliche client setups ignoriert.

[DeddyH]

Zitat:

Zitat von jobo (Beitrag 1171722)

Es schadet aber auch nicht, zu wissen, was die NLS Settings sind.

Ich habe ja auch nirgends das Gegenteil behauptet :zwinker:

[jobo]

Ich hab ja auch nirgends behauptet, dass Du behauptet hast, dass ..

Der Anfang eine rekursiven Diskussion. :stupid:

Also ernst, NLS Settings oder Lokalisierung von DB und Clients allgemein, kann in der Bedeutung sehr leicht unterschätzt werden. Meine Betonung dieses Problems galt nicht Dir, sondern schlicht allen Datenbankentwicklern auf der Welt. :)

[Jumpy]

Clientseitig ist da nix zu machen, weil das eine Verarbeitung ist, die große SQl-Statements jeweils aus einer Datei lädt an die DB schickt. Da werden auf Selects basierend neue Tabellen erstellt, worauf dann spätere SQL-Statements zugreifen und Selects erzeugen, die dann letztlich zurückgeliefert werden und die dann direkt von der Query in eine CSV geschrieben werden. Es ist daher nicht bekannt, welche Felder es gibt und welche Felder ggf. in dem Format kommen, das bearbeitet werden müsste. Daher ist clientseitig in der Anwendung da nichts machbar.

Problem ist ja prinzipiel gelöst, nachdem die NLS Settings in der Registry angepasst wurden. Ist aber für mich eine Lehre gewesen, so dass meine zukünftigen ähnlichen Verarbeitungen zunächst die Settings checken und dann Meldung machen, falls diese falsch sind. Mit jobo's Tip kann die Verarbeitung aber dann trotzdem mit temporären Settings weitermachen, bis mal einer dazukommt, den entsprechenden Rechner richtig einzustellen. :thumb:

Nur aus Neugier aber immer noch die Frage:
Gibt's ein eleganteres Konstrukt zur Erzwingung von Nachkommastellen. Was ich so gegoogelt habe muss immer irgendwie eine konvertierung in einen "String" stattfinden durch sowas wie Format und das ist ja in Oracle To_Char. Die Cast-Funktion tut's da (in Oracle) auch nicht, in anderen DB's soll's da mehr Möglichkeiten geben.

[jobo]

Zitat:

Zitat von Jumpy (Beitrag 1171672)

SQL-Code:

Select
TRIM(TO_CHAR(BETRAG_EU,999999999999990.99)) AS WERT
From
TABELLE

Ich hab was wichtiges übersehen, die Funktion oben verwendet eine falsche Notation. Die Format Maske gehört in Anführungsstriche. Ist das im Echtsystem auch so?

Was da in diesem Fall genau geschieht, kann ich nicht richtig nachvollziehen, aber auch hier wird offenbar implizit (falsch) konvertiert und zwar die Formatmaske selbst von Zahl zu Text.

Hier mal ein paar Beispiele:

Code:

Connected to Oracle Database 10g Release 10.2.0.2.0 
Connected as jo@db
 
SQL> 
SQL> -- my nls
SQL> select * from nls_session_parameters;
 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   GERMAN
NLS_TERRITORY                  GERMANY
NLS_CURRENCY                   ¿
NLS_ISO_CURRENCY               GERMANY
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                dd.mm.yyyy
NLS_DATE_LANGUAGE              GERMAN
NLS_SORT                       GERMAN
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              ¿
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
 
17 rows selected
SQL> -- default NLS decimal separators
SQL> alter session set NLS_NUMERIC_CHARACTERS =',.'; -- default settings
 
Session altered
SQL> Select 88.1234 as MyNumber, 999999999999990.99 as MyMasknumber,
  2         TRIM(TO_CHAR(88.1234, 999999999999990.99)) as MyExplizitConvertedNumber,
  3         to_char(999999999999990.99) as MyMaskNumberImplizitConverted
  4    From dual;
 
  MYNUMBER MYMASKNUMBER MYEXPLIZITCONVERTEDNUMBER MYMASKNUMBERIMPLIZITCONVERTED
---------- ------------ ------------------------- -----------------------------
   88,1234 999999999999 0,88                      999999999999990,99
SQL> alter session set NLS_NUMERIC_CHARACTERS ='.,';
 
Session altered
SQL> Select 88.1234 as MyNumber, 999999999999990.99 as MyMasknumber,
  2         TRIM(TO_CHAR(88.1234, 999999999999990.99)) as MyExplizitConvertedNumber,
  3         to_char(999999999999990.99) as MyMaskNumberImplizitConverted
  4    From dual;
 
  MYNUMBER MYMASKNUMBER MYEXPLIZITCONVERTEDNUMBER MYMASKNUMBERIMPLIZITCONVERTED
---------- ------------ ------------------------- -----------------------------
   88,1234 999999999999 88.12                     999999999999990.99
SQL> alter session set NLS_NUMERIC_CHARACTERS =',.'; -- Default
 
Session altered
SQL> -- do the same with corrected format mask
SQL> Select 88.1234 as MyNumber, 999999999999990.99 as MyMasknumber,
  2         TRIM(TO_CHAR(88.1234, '999999999999990.99')) as MyExplizitConvertedNumber,
  3         to_char(999999999999990.99) as MyMaskNumberImplizitConverted
  4    From dual;
 
  MYNUMBER MYMASKNUMBER MYEXPLIZITCONVERTEDNUMBER MYMASKNUMBERIMPLIZITCONVERTED
---------- ------------ ------------------------- -----------------------------
   88,1234 999999999999 88.12                     999999999999990,99
SQL> alter session set NLS_NUMERIC_CHARACTERS ='.,';
 
Session altered
SQL> -- do the same with corrected format mask
SQL> Select 88.1234 as MyNumber, 999999999999990.99 MyMasknumber,
  2         TRIM(TO_CHAR(88.1234, '999999999999990.99')) MyExplizitConvertedNumber,
  3         to_char(999999999999990.99) as MyMaskNumberImplizitConverted
  4    From dual;
 
  MYNUMBER MYMASKNUMBER MYEXPLIZITCONVERTEDNUMBER MYMASKNUMBERIMPLIZITCONVERTED
---------- ------------ ------------------------- -----------------------------
   88,1234 999999999999 88.12                     999999999999990.99
 
SQL>

[Jumpy]

Der Formatstring war tatsächlich ohne Gänsefüßchen.
Das ist mir vor lauter Länge gar nicht aufgefallen.

Auch wenn nicht erklärbar ist, was da falsch läuft (sprich die verschiebung der Nachkommastellen) zeigt dein Beispiel, dass bei korrektem Formatstring die Änderung der Lokalisierung tatsächlich nur Komma und Punkte vertauscht und nicht für die Verschiebung der Nachkommastelle verantwortlich ist.

Danke für die Zusatzinfo.:thumb:

[jobo]

Fakt ist, der Funktionsaufruf ist in der Form as P#1 falsch. Warum das von Oracle ohne Fehlermeldung geschluckt wird, kann ich nicht verstehen.

Der falsch typisierte Parameter wird also offenbar auch konvertiert (von Zahl zu Text), das kann nur zufällig gut gehen, wenn nämlich der Parameter (eigentlich Formatmaske) keine Aplha Zeichen enthält.

So oder so, implizite Typkonvertierung ist ein Graus. Wenn es das nicht gäbe, wären aber wahrscheinlich 50% der Datenbankentwickler arbeitslos.
:)

[Jumpy]

Zitat:

Zitat von jobo (Beitrag 1171918)

Der falsch typisierte Parameter wird also offenbar auch konvertiert (von Zahl zu Text), das kann nur zufällig gut gehen, wenn nämlich der Parameter (eigentlich Formatmaske) keine Aplha Zeichen enthält.

Ist so. Hab aus Neugier nochmal rumgespielt.

Aus TO_CHAR(12122.22,999990.99)
wird TO_CHAR(12122.22,TO_CHAR(999990.99))
wird bei dt. Lokalisierung zu TO_CHAR(12122.22,'999990,99'))

Bleibt die Frage, warum er dabei aus dem Ergebnis 121,22 macht, anstatt 12122,22. Es findet da auch eine Rundung statt: 12122.82 wird zu 121.23.
Bei '.' oder 'D' an der Stelle des Kommas "wird alles gut".