1

I have a TABLE with two columns:

ID      DESCRIPTION
1       first line
2       second line
3       _
4       1 first numeric line

and I have a query 

SELECT * FROM TABLE ORDER BY DESCRIPTION

The problem is that I retrieve different result depending on the machine where I run the code. If I run the code on

  • linux (centos) I get the following result
    ID      DESCRIPTION
    4       1 first numeric line
    3       _
    1       first line
    2       second line

  • windows (7) I get the following result
    ID      DESCRIPTION
    3       _
    1       first line
    2       second line
    4       1 first numeric line

  • MAC OS(Snow Leopard) i get the folowing result
    ID      DESCRIPTION
    3       _
    1       first line
    2       second line
    4       1 first numeric line

So as you can see the underscore "_" is treat different depending on the machine where I run the query :(.
Is this a problem of the driver? Why i don't get the same result?

duderoot
  • 977
  • 1
  • 8
  • 23
  • 3
    Perhaps a client side environment issue setting a different NLS_LANG or NLS_SORT in the session? It would be nice if you could run the query from sqlplus on both environments, see if the result is the same, and if so, then try again with the same "alter session set nls_lang..." on both environments before the query. – Glenn Aug 02 '12 at 21:49
  • I'm assuming the Database is centralised and all the clients are calling the same database? – MadProgrammer Aug 02 '12 at 22:02
  • See [the documentation](http://docs.oracle.com/cd/E14072_01/appdev.112/e10766/tdddg_globalization.htm#i1006280) for more about Glenn's comment. Your Java locale affects this setting; see this [question](http://stackoverflow.com/questions/1079985/nls-lang-setting-for-jdbc-thin-driver) for example. – Alex Poole Aug 03 '12 at 07:28

2 Answers2

1

10x for the hints. Indeed the problem was with the setting of locale, i had:

  • on linux

    LANG=en_US.UTF-8

    LC_CTYPE="en_US.UTF-8"

  • on mac

    LANG=de_DE.UTF-8

    LC_CTYPE="de_DE.UTF-8"

After setting locale on linux machine on german utf-8 i had the same result on linux as on the other machine ( mac and windows) and now build is back to stable :).

I will try to play with the config of NLS_LANG in order to be able to set the locale of the linux machine back to english utf-8.

hol
  • 8,255
  • 5
  • 33
  • 59
duderoot
  • 977
  • 1
  • 8
  • 23
1

The order depends on your NLS settings. See the NLS_LANG and NLS_SORT setting. If you provide the same NLS setting on all environments the order will be the same.

You may also specify the order explicitly:

SELECT * 
  FROM TABLE 
 ORDER BY NLSSORT(Description, 'NLS_SORT = BINARY')

I expect the performance of the sort is not important here, because the query may not use the index on the description column if the column is indexed.

Grzegorz Kazior
  • 371
  • 8
  • 12