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?
Best Answer
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
and bounce the instance. You can always override this in the session using
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
, andNLS_SESSION_PARAMETERS
. To check the setting in the current session you can also runshow parameter nls_language
.