Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Strange ORDER BY behaviour

Re: Strange ORDER BY behaviour

From: Laurenz Albe <invite_at_spam.to.invalid>
Date: 30 Aug 2007 12:04:54 GMT
Message-ID: <1188475490.983462@proxy.dienste.wien.at>


Carlos <miotromailcarlos_at_netscape.net> wrote:
>> I'm running on 10g Express Edition 10.2.0.1.0
>>
>> There is some strange behaviour of the ORDER BY clause in the
>> following example. I'm executing all SQL via JDBC.
>>
>> create table test (foo varchar2(3))
>>
>> insert into test values('AAA')
>> insert into test values('BBB')
>> insert into test values('CCC')
>> insert into test values('111')
>> insert into test values('222')
>> insert into test values('333')
>>
>> select * from test where foo > '200' order by foo
>>
>> But what I get is this:
>> AAA
>> BBB
>> CCC
>> 222
>> 333
>>
>> The selection is as I expected it: the value "111" is not included.
>> But the ORDER BY seems to use a different charset!?!
>> As far as I know, the parameters nls_sort and nls_comp are important
>> for this.
>>
>> select * from nls_session_parameters order by parameter
>>
>> NLS_COMP BINARY
>> NLS_SORT GERMAN

>
> The answer is NLS_SORT:

... but NLS_COMP also plays a role:

ALTER SESSION SET NLS_COMP='LINGUISTIC'; ALTER SESSION SET NLS_SORT='GERMAN'; SELECT foo FROM test WHERE foo > '200' ORDER BY foo;

FOO



222
333

If you want comparison and sort to work consistently, the two parameters must be set consistently (BINARY with BINARY and LINGUISTIC with GERMAN).

In the latter case you may consider using a linguistic index on your text columns:

CREATE INDEX test_foo_ind_de ON test(NLSSORT(foo, 'NLS_SORT = German'));

Yours,
Laurenz Albe Received on Thu Aug 30 2007 - 07:04:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US