2 Replies Latest reply: Dec 24, 2012 4:02 AM by Robin R RSS

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

    Robin R
      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