5

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?

  • 1
    I'm not particularly versed in Oracle, but have you checked the collation settings? docs.oracle.com/cd/E18283_01/server.112/e17118/queries005.htm – Jonathan Fite Jan 5 '16 at 16:25
  • Edit your question with the output of select * from NLS_INSTANCE_PARAMETERS , select * from NLS_DATABASE_PARAMETERS and select * from NLS_SESSION_PARAMETERS – Philᵀᴹ Jan 5 '16 at 16:47
  • Does your query have an order by clause? The natural-sort tag seems to imply that you believe that there is some "natural" way in which data is sorted in the absence of an order by. – Justin Cave Jan 5 '16 at 16:51
  • yeah, the qeuries have order by clauses. I just thought that the NLS_SORT and NLS_LANGUAGE are not environmental, but per session right? So how can I check those values for the application session? – Jeroen Jan 5 '16 at 16:56
  • @Phil, I added the information you requested. – Jeroen Jan 6 '16 at 9:25
5

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.

  • how do I view the current value in system? since select * from system is not working (even with system user account). What do you mean with "bounce the instance?" – Jeroen Jan 6 '16 at 8:56
  • 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 – cba Jan 6 '16 at 9:28
4

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.

  • the sorting difference is indeed with queries with an order by clause. I need to check the system setting for nls_sort? – Jeroen Jan 5 '16 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.