Strange ORDER BY behaviour

Hi!
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

One could assume that this would return the following: 222
333
AAA
BBB
CCC Because in most ASCII-like charsets the numbers are smaller than the letters. 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_CALENDAR	GREGORIAN
NLS_COMP	BINARY
NLS_CURRENCY	?
NLS_DATE_FORMAT	DD.MM.RR
NLS_DATE_LANGUAGE	GERMAN
NLS_DUAL_CURRENCY	?
NLS_ISO_CURRENCY	GERMANY
NLS_LANGUAGE	GERMAN
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE
NLS_NUMERIC_CHARACTERS	,.
NLS_SORT	GERMAN
NLS_TERRITORY	GERMANY
NLS_TIME_FORMAT	HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT	DD.MM.RR HH24:MI:SSXFF
NLS_TIMESTAMP_TZ_FORMAT	DD.MM.RR HH24:MI:SSXFF TZR
NLS_TIME_TZ_FORMAT	HH24:MI:SSXFF TZR

select * from v$nls_parameters
NLS_CHARACTERSET	WE8MSWIN1252

The value of NLS_COMP is BINARY, and I want it that way. But why does the ORDER BY use a different sorting than the WHERE clause? In WE8MSWIN1252, are the numbers sorted before or after letters?

Thanks for any help! Received on Thu Aug 30 2007 - 03:46:02 CDT