Toad World® Forums

Ignored NLS_NUMERIC_CHARACTERS


#1

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?

Thank you in advance.


#2

Hello,

Open the Preferences dialog and under User Interface find Date displayed format. It provides a selection of date formats to choose from.

Hope this helps,
Roman


#3

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.

Any idea?


#4

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.

Hope this helps,
Roman