| Oracle and German [message #475264] |
Tue, 14 September 2010 01:32  |
ksu04257
Messages: 1
Registered: September 2010
|
Junior Member |
|
|
Hi!
I have a Problem with the nls_numeric_characters.
I have a database-table that contains float-values (type: BINARY_DOUBLE). When I select the data from this table, I get a result of my colums in e.g. 1,3E-001 (with comma).
But when I select the data with a where-condition (where column > 1,0) I get error-messages (ORA-00933: Sql-Statement not ended properly), when changing the order (where 1,0 < column) I get the error message ORA-00920 - Invalid parameter. When I change the comma into a . (Periode), the command is properly executed.
My nls_session_parameters are:
NLS_LANGUAGE
GERMAN
NLS_TERRITORY
GERMANY
NLS_NUMERIC_CHARACTERS
,.
In registry I have set NLS_LANG=German_Germany.UTF8
All regional settings in Windows are set to German. Dezimal Seperator is , (comma).
Can someone help?
Thanks in advance
Sabine
|
|
|
|
|
|
| Re: Oracle and German [message #475280 is a reply to message #475264] |
Tue, 14 September 2010 02:41  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
One example to make it more clear:
Which selects two columns from dual.
To turn the "," into a decimal separator you would not only need to change the decimal character, you would have to disable the "," from being a SQL syntax character.
you could of course do something like:
SQL> alter session set nls_numeric_characters=',.';
Session altered.
SQL> SELECT to_number('1,2') FROM dual;
TO_NUMBER('1,2')
----------------
1,2
SQL> alter session set nls_numeric_characters='.,';
Session altered.
SQL> SELECT to_number('1.2') FROM dual;
TO_NUMBER('1.2')
----------------
1.2
|
|
|
|
| Re: Oracle and German [message #475402 is a reply to message #475280] |
Tue, 14 September 2010 17:24 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following just expands the previous example.
SCOTT@orcl_11gR2> ALTER SESSION SET NLS_LANGUAGE = 'GERMAN'
2 /
Session altered.
SCOTT@orcl_11gR2> ALTER SESSION SET NLS_TERRITORY = 'GERMANY'
2 /
Session altered.
SCOTT@orcl_11gR2> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.'
2 /
Session altered.
SCOTT@orcl_11gR2> CREATE TABLE database_table
2 (column_name NUMBER)
3 /
Table created.
SCOTT@orcl_11gR2> INSERT INTO database_table VALUES (TO_NUMBER ('1,2'))
2 /
1 row created.
SCOTT@orcl_11gR2> SELECT * FROM database_table
2 /
COLUMN_NAME
-----------
1,2
1 row selected.
SCOTT@orcl_11gR2> SELECT * FROM database_table
2 WHERE column_name > TO_NUMBER ('1,0')
3 /
COLUMN_NAME
-----------
1,2
1 row selected.
SCOTT@orcl_11gR2> SELECT * FROM database_table
2 WHERE TO_NUMBER ('1,0') < column_name
3 /
COLUMN_NAME
-----------
1,2
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
