This discussion is archived
2 Replies Latest reply: Dec 24, 2012 2:02 AM by Robin R RSS

Strange behavior NLS settings when Run Script (F5) - compared to SQL Plus

Robin R Newbie
Currently Being Moderated
Hello,

(Concerns SQL Developer 3.2.20.09 on Oracle database 10.2.0.3.0.)

I could not get the NLS preference setting for Decimal Separator to work as I would expect (as SQL Plus) when using Run Script (F5). To demonstrate, I disabled NLS settings in Preferences (checkbox 'Skip NLS Settings' turned on). Please refer following script output (spool) from SQL Plus:

SQL> desc datatabel;
Name Null? Type
----------------------------------------- -------- ----------------------------
NUMMER NUMBER
WAARDE VARCHAR2(20)
DATUM DATE
NUMMERDEC NUMBER(10,2)

SQL> alter session set nls_numeric_characters = '.,';

Session altered.

SQL> SELECT * FROM nls_session_parameters where parameter='NLS_NUMERIC_CHARACTERS';

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS .,

SQL> select * from datatabel;

NUMMER WAARDE DATUM NUMMERDEC
---------- -------------------- --------- ----------
1 waarde1 11-FEB-10 1.1
2 waarde2 04-FEB-10 1.2
3 waarde3 05-FEB-10 1.3
4 waarde4 03-FEB-10 1.11

SQL> alter session set nls_numeric_characters = ',.';

Session altered.

SQL> SELECT * FROM nls_session_parameters where parameter='NLS_NUMERIC_CHARACTERS';

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS ,.

SQL> select * from datatabel;

NUMMER WAARDE DATUM NUMMERDEC
---------- -------------------- --------- ----------
1 waarde1 11-FEB-10 1,1
2 waarde2 04-FEB-10 1,2
3 waarde3 05-FEB-10 1,3
4 waarde4 03-FEB-10 1,11

SQL> spool off


Compare this with SQL Developer Script Output:

desc datatabel
Name Null Type
--------- ---- ------------
NUMMER NUMBER
WAARDE VARCHAR2(20)
DATUM DATE
NUMMERDEC NUMBER(10,2)

session SET altered.
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS .,

NUMMER WAARDE DATUM NUMMERDEC
---------- -------------------- -------- ----------
1 waarde1 11-02-10 1.1
2 waarde2 04-02-10 1.2
3 waarde3 05-02-10 1.3
4 waarde4 03-02-10 1.11

session SET altered.
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS ,.

NUMMER WAARDE DATUM NUMMERDEC
---------- -------------------- -------- ----------
1 waarde1 11-02-10 1.1
2 waarde2 04-02-10 1.2
3 waarde3 05-02-10 1.3
4 waarde4 03-02-10 1.11


I would expect SqlDeveloper would have presented the NUMMERDEC column with a comma in the last SELECT statement, as SQL Plus did. BTW, Query Result (F9) in sql developer will present the NUMMERDEC column with a comma.

Is this expected behavior, or should SQL Developer react just as SQL Plus?

Thanks and regards,

Robin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points