Oracle sort order changed

Question

We recently deployed a new environment with a newer version of Oracle (12c) instead of 11r2.

Only recently I noticed my data returned is sorted differently.

Instead of having small letters, capital letters and numbers... (on Oracle 11r2) I get my data sorted the opposite way: numbers, capital letters, small letters (Oracle 12c)

This is realy confusing the end-user of various applications querying the database. What settings do I need to check/compare to fix this problem in Oracle 12c?

The queries I refer to have an order by clause. EDIT The settings on for these parameters are the same on both evironments:

NLS_SORT type: string value:

nls_language type: string value: DUTCH

So there is no value for NLS_SORT, what is the default then?

@phil

select * from NLS_INSTANCE_PARAMETERS;

Parameter                     Oracle11r2          Oracle12c
NLS_LANGUAGE                       DUTCH              DUTCH
NLS_TERRITORY            THE NETHERLANDS    THE NETHERLANDS
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP                          BINARY           BINARY
NLS_LENGTH_SEMANTICS                BYTE             CHAR
NLS_NCHAR_CONV_EXCP                FALSE            FALSE

select * from NLS_SESSION_PARAMETERS;

these are the same as my NLS settings in Oracle SQL developer, so not very relevant I guess...

select * from NLS_DATABASE_PARAMETERS;

These are the same, only NLS_RDBMS_VERSION differs

So only NLS_LENGTH_SEMANTICS differs. Is there a way to check the values for other sessions?

Answer 1

NLS_LANGUAGE determines the sort order. If you've went from, for example, FRENCH to the defaut, AMERICAN, you would get the difference in sort order you describe. To set the default NLS_LANGUAGE execute

alter system set nls_language ='FRENCH' scope=spfile;

and bounce the instance. You can always override this in the session using

alter session set nls_language = 'FRENCH';

To bounce the instance is to restart it. You need to do that when changing some system parameters like NLS_LANGUAGE. If you only change the parameter in the current session you don't need to restart the instance.

To check the parameter values you can, like Phil said earlier, query NLS_DATABASE_PARAMETERS, NLS_INSTANCE_PARAMETERS, and NLS_SESSION_PARAMETERS. To check the setting in the current session you can also run show parameter nls_language.

Answer 2

Unless your queries are ordered (with an explicit ORDER BY ) the data returned from queries is not guaranteed to be in any order. They just happen to come out in a certain order and that order is typically static unless something massively changes in the tables or in the RDBMS kernel. This is not unique to ORACLE.

If the queries expect ordered data, I would check NLS settings to make sure that collation order is set. If needed, alter the NLSSORT function manually.