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 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

NLS based sorting in oracle

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

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';

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 ...

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

Collapse -

NLS based sorting in oracle

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

Call Larry Ellison. He could help, for sure.

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 ...

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.

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 ...

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.

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 ...

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

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

Related Discussions

Related Forums