General discussion

Locked

NLS based sorting in oracle

By manish_dhall ·
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(3 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

This conversation is currently closed to new comments.

16 total posts (Page 2 of 2)   Prev   01 | 02
Thread display: Collapse - | Expand +

All Comments

Collapse -

NLS based sorting in oracle

by manish_dhall In reply to NLS based sorting in orac ...

Poster rated this answer

Collapse -

NLS based sorting in oracle

by James_Randy In reply to NLS based sorting in orac ...

This looks like a job for Super Tech. Give him a call at 555-5555

Collapse -

NLS based sorting in oracle

by manish_dhall In reply to NLS based sorting in orac ...

Poster rated this answer

Collapse -

NLS based sorting in oracle

by James_Randy In reply to NLS based sorting in orac ...
Collapse -

NLS based sorting in oracle

by manish_dhall In reply to NLS based sorting in orac ...

Poster rated this answer

Collapse -

NLS based sorting in oracle

by manish_dhall In reply to NLS based sorting in orac ...

This question was closed by the author

Back to Web Development Forum
16 total posts (Page 2 of 2)   Prev   01 | 02

Related Discussions

Related Forums