Oracle NLS_SORT not working via ALTER SESSION?

Question

Using 'alter session set nls_sort' does not seem to be working for me. I am using SQLPlus v11.2.0.3.0 x64 and trying to apply the simple steps for 'Example 9-10 NLS_SORT Affects Linguistic Sort Order' found in the Oracle documentation at http://docs.oracle.com/cd/E18283_01/appdev.112/e10766/tdddg_globalization.htm#CACJEJIB

CREATE TABLE temp (name VARCHAR2(15));

INSERT INTO temp (name) VALUES ('laguna');
INSERT INTO temp (name) VALUES ('llama');
INSERT INTO temp (name) VALUES ('loco');

SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_SORT';
Result: BINARY

SELECT * FROM temp ORDER BY name;
Result:
NAME
---------------
laguna
llama
loco

ALTER SESSION SET NLS_SORT=SPANISH_M;
SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_SORT';
Result: SPANISH_M

SELECT * FROM temp ORDER BY name;
Results are the same:
NAME
---------------
laguna
llama
loco

According to the doco, the sort order above should have changed but it did not. But, if I apply the NLS_SORT as part of the query itself I get the correct resutls:

SELECT * FROM temp ORDER BY NLSSORT(name, 'NLS_SORT=SPANISH_M');
Result:
NAME
---------------
laguna
loco
llama

What am I missing here? Thx in advance.

This is a weird problem. I can't reproduce it, either on my machine or on SQL Fiddle. Maybe you're hitting some weird bug. Do you get the same issue with different clients? You may want to post your client and database version, as well as the results from select name, value from v$parameter where name like '%nls%';. — Jon Heller
I can neither reproduce it. I tried it in SQLPlus and SQLDeveloper. It should really work. My version is Oracle Database 11g Enterprise Edition Release 11.2.0.2.0. — hol
I got it to work using 11.2.0.2.0 also. Odd thing, it works fine for one coworker using 11.2.0.3.0 but not for another. I will attempt to uninstall/reinstall. — Paul
What exactly are the differences between the environments that work and the environments that don't? Is this just a difference in clients, or are the problems on different servers? If it's a server issue, this is a long-shot but you may want to check into Oracle Locale Builder. Every imaginable NLS setting is configurable. It's possible that one server has a custom, broken version of SPANISH_M. If you load the linguistic sort and look at the Unicode Collation Sequence, you'll see how the LL and L sort is implemented. It might give you some idea about how to further troubleshoot. — Jon Heller
nls_language AMERICAN nls_territory AMERICA nls_sort BINARY nls_date_language AMERICAN nls_date_format DD-MON-RR nls_currency $ nls_numeric_characters ., nls_iso_currency AMERICA nls_calendar GREGORIAN nls_time_format HH.MI.SSXFF AM nls_timestamp_format DD-MON-RR HH.MI.SSXFF AM nls_time_tz_format HH.MI.SSXFF AM TZR nls_timestamp_tz_format DD-MON-RR HH.MI.SSXFF AM TZR nls_dual_currency $ nls_comp BINARY nls_length_semantics BYTE nls_nchar_conv_excp FALSE — Paul

Paul Paul · Accepted Answer · 2013-12-03T20:48:36

This was resolved whe I uninstalled Oracle v11.2.0.3.0 and installed v12.1.0.1.0. Truth be told, I can't rule out that it may have just been a problem with our wrapper/configuration of the v11.2.0.3.0 Oracle installer or Oracle itself. Thx for all the post just the same.

Oracle NLS_SORT not working via ALTER SESSION?

2 Answers