Good morning,
I’m struggling with a problem related to decimal separator.
Independently on which NLS_NUMERIC_CHARACTERS I’m setting (’.,’/’,.’/’ ,’/’ .’)
The following query always returns: 1000,5 (with comma)
SELECT 2001/2 FROM DUAL;
This problem is driving me crazy cause I often have to export records in INSERT INTO format and everytime a coloumn has a decimal value, I fall in this defect:
INSERT INTO TABLE (SINGLE_COLOUMN) VALUES (1000,5);
That, obviously, ends up with error “ORA-00913: too many values”.
Is there something I have to modify/edit/configure to prevent this behaviour?
Hi Roman,
thank you for your answer but I think I badly explained my problem.
My problem concerns numbers with decimal parts.
When I try to divide the number 2001 by 2 using this select:
SELECT 2001/2 FROM DUAL
I get a number with a decimal part with COMMA separator: this number is "1000,5"
Nothing unusual so far but, when I try to change NLS_NUMERIC_CHARACTERS in order to get a different decimal separator, it doesn't work.
Infact, look at the following commands...
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=",.";
SELECT 2001/2 FROM DUAL;
The result still 1000,5
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".,";
SELECT 2001/2 FROM DUAL;
The result still 1000,5
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=". ";
SELECT 2001/2 FROM DUAL;
The result still 1000,5
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=" .";
SELECT 2001/2 FROM DUAL;
The result still 1000,5
If I use sqlplus by command line all works fine, so I think there is something in SQLNavigator 6.1 which overwrites NLS_NUMERIC_CHARACTERS configuration.
Sorry, I pointed you to the wrong preference. There’s also preference Retrieve all fields as strings under Code Editor\SQL Scripts. We usually discourage users from applying this preference, but it can help in your situation.