Oracle RDBMS and NLS support question

From: Dieter Oberkofler <dtr_at_leadingbits.via.at>
Date: 1996/03/09
Message-ID: <31418A77.2D9E_at_leadingbits.via.at>#1/1

I am wondering why Oracle makes a difference between the NLS conversion mechanism used in the "where clause" and the one used in the "order by clause" when executing an sql select statement.

I use Oracle 7.1 on a Novell NetWare Server with the NLS Support for the German language active on the workstations.
(NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1)
The "order by" works perfectly and sorts the german umlaute
(eg. Ä, Ö, Ü) after the corresonding characters (eg. AE, OE, UE).

The "where" does not use the NLS convertion rules at all and all the german umlaute are sortet at the end of all "normal" ansi characters.

In order to make the where clause work correctly i must use in all selects the NLSSORT function to convert the strings according to the NLS sort order manually.

This makes it impossible to use this columns in an index because the index is stored without the correct NLS convertion.

The same happens when using function like min and max.

Any feedback gratefully received.

• START OF EXAMPLE =====

rem CREATE TABLE
drop table NLSDemo;
create table NLSDemo (

Id            numeric(3,0) primary key,
Match         varchar2(30) not null,
MatchNLS      raw(60) default '00');

rem CREATE INDEX
drop index NLSIndex;
create index NLSIndex on NLSDemo(Match); drop index NLSIndexNLS;
create index NLSIndexNLS on NLSDemo(MatchNLS);

rem CREATE SOME ROWS

insert into NLSDemo values (100, 'Österreich',          Null);
insert into NLSDemo values (110, 'Obernberger Brief',   Null);
insert into NLSDemo values (120, 'LEADING BITS',        Null);
insert into NLSDemo values (130, 'Ãœberreiter Media',    Null);
insert into NLSDemo values (140, 'CASH FLOW',           Null);
insert into NLSDemo values (150, 'Ärtztewoche',         Null);
insert into NLSDemo values (160, 'KRONE',               Null);
insert into NLSDemo values (170, 'KURIER',              Null);
insert into NLSDemo values (180, 'ÖMV Magazin',         Null);
insert into NLSDemo values (190, 'FALTER VERLAG',       Null);

update NLSDemo set MatchNLS = NLSSORT(Match, 'NLS_SORT = german'); commit;

rem THIS SELECT WORKS AS EXPECTED
select Id, Match from NLSDemo
order by Match;

rem THIS SELECT RETURNS ALSO THE ROWS WITH GERMAN UMLAUTE select Id, Match from NLSDemo
where Match >= 'LEADING BITS'
order by Match;

rem THIS SELECT WORKS WITHOUT USING THE INDEX select Id, Match from NLSDemo
where NLSSORT(Match, 'NLS_SORT = German') >= NLSSORT('LEADING BITS', 'NLS_SORT = German')
order by Match;

rem THIS SELECT WORKS WITH AN EXTRA COLUMN select Id, Match from NLSDemo
where MatchNLS >= NLSSORT('LEADING BITS', 'NLS_SORT = German') order by Match;
===== END OF EXAMPLE =====   Dieter Oberkofler
Software Engineer

---

LEADING BITS GmbH.               Tel             (+43-1) 586 76 11
Schleifmuehlgasse 5/17           Fax             (+43-1) 587 76 15
A-1040 Vienna                    E-mail     dtr_at_leadingbits.via.at
Austria                          Compuserve            100141.1314
------------------------------------------------------------------

Received on Sat Mar 09 1996 - 00:00:00 CET