Discussion Locked

NLS based sorting in oracle

0 helpful
16 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am trying to do Linguistic sorting (specific to language)in Oracle(enterprise edition 8.1.6.0.0)on NT 4.0 service pack 4.0.
But i am getting results based on binary sort.List of commands tried are as under:
Database character set - UTF-8
National character set - UTF-8
create table prototest (
empid number(38) primary key ,
empname varchar2(40) not null,
joining date not null,
salary number(20,2) not null ,
age integer not null ,
remarks varchar2(200) not null
);
CREATE INDEX nls_index ON prototest (NLSSORT(empname, 'NLS_SORT = French'));
insert into prototest
(
EMPID,
EMPNAME,
JOINING,
SALARY,
AGE,
REMARKS
)
values
(
1,
'peach',
to_date('2000-09-01','YYYY-MM-DD'),
3000.00,
30,
'French'
)
;
EMPNAME field has following values for other rows:
'p?ch?','p?che','sin'
Here the binary order is
peach
p?ch?
p?che
sin
whereas the French order is
peach
p?che
p?ch?
sin

1.SELECT * FROM prototest WHERE
NLSSORT(empname,'NLS_SORT=French') >
NLSSORT('p?ch?','NLS_SORT=French');

2.SELECT /*+index(prototest.nls_index)*/
* FROM prototest WHERE
NLSSORT(empname,'NLS_SORT=French') >
NLSSORT('p?ch?','NLS_SORT=French')

No change, even when using the hint.
3.SQL> select * from prototest order by empname
gives the same result.
4.The steps were done again setting NLS_LANG parameter in the OS environment as French_France.WE8ISO8859P1 and the database character set/national character set to WE8ISO8859P1.
But no luck, yet again
5.Query Optimizer commands...
alter session set sql_trace = true;
alter session set optimizer_mode = All_Rows;
ANALYZE TABLE prototest ESTIMATE STATISTICS;
ANALYZE TABLE PROTOTEST VALIDATE STRUCTURE CASCADE;
SELECT * FROM PROTOTEST WHERE NLSSORT(EMPNAME, 'NLS_SORT = FRENCH') > NLSSORT('p?ch?','NLS_SORT = FRENCH');
Please suggest a way out.
Thanks and Regards,
Manish Dhall

16 replies

by lde

Probably following statement can solve your troubles:

alter session set nls_sort='French';

Binary sorting can be enabled by

alter session set nls_sort='BINARY';

by manish_dhall

i have tried alter session set nls_sort='french'. and set nls_comp=ansi.
but still the result is binary sort in place of linguistic sort.
Regards,
Manish Dhall

by james_randy

Since your question is so old, you might as well just reload the latest versions of all software, and you'll have a new set of problems to deal with. Heck, update all your hardware and operating systems while you're at it. Have a real party.

by james_randy

Since your question is so old, you might as well just reload the latest versions of all software, and you'll have a new set of problems to deal with. Heck, update all your hardware and operating systems while you're at it. Have a real party.

by james_randy

If you are still tackling this problem, perhaps you should consider calling your local super-hero

This discussion is closed to new replies.