Weird Oracle orderBy behavior on String with * and (

Question

If I run this query :

select distinct column_value as test 
from table(sys.dbms_debug_vc2coll('AFGHANISTAN','*AFRIQUE','(AUCUN)','ITALIE','**MONDE')) 
order by test asc;

I would expect either :

(AUCUN)
**MONDE
*AFRIQUE
AFGHANISTAN
ITALIE

Or

AFGHANISTAN
ITALIE
*AFRIQUE
**MONDE
(AUCUN)

But this what I get :

AFGHANISTAN
*AFRIQUE
(AUCUN)
ITALIE
**MONDE

So it looks as it's ignoring ( and * completely.

If I change my session NLS_SORT param from FRENCH to BINARY

alter session set NLS_SORT=BINARY;

The order is fine.

But I'm not really supposed to alter my customer database settings, is there some reason for getting this weird order by default?

And can I do anything through JPA criteria API to get the good order without altering the database?

EDIT : This is the Expression to use for order :

// My Original field expression used for the sort
Expression<String> myFieldExp = root.get("mySortField");
Expression<String> sortExpWithNLSfix = builder.function("nlssort", String.class, myFieldExp , builder.literal("nls_sort=binary"));
myCritQuery.orderBy(builder.asc(sortExpWithNLSfix);

that expression was really helpfull – Robin Dijkhof Mar 11 '21 at 09:47 — Robin Dijkhof, Mar 11 '21 at 09:47

score 2 · Accepted Answer · answered May 27 '16 at 16:23

It must be something to do with the way the French nls_sort works. You can, however, use the nlssort function in the order by to change the sort mode for just the query, like so:

alter session set nls_sort=french;

select distinct column_value as test
from table(sys.dbms_debug_vc2coll ('AFGHANISTAN',
                                   '*AFRIQUE',
                                   '(AUCUN)',
                                   'ITALIE',
                                   '**MONDE'))
order by test asc;

TEST          
--------------
AFGHANISTAN   
*AFRIQUE      
(AUCUN)       
ITALIE        
**MONDE

select distinct column_value as test
from table(sys.dbms_debug_vc2coll ('AFGHANISTAN',
                                   '*AFRIQUE',
                                   '(AUCUN)',
                                   'ITALIE',
                                   '**MONDE'))
order by nlssort(test, 'nls_sort=binary') asc;

TEST          
--------------
(AUCUN)       
**MONDE       
*AFRIQUE      
AFGHANISTAN   
ITALIE

Thank you, indeed that works fine in SQL. Now I'm wondering how can i make this through JPA criteria api — TheBakker, Jun 01 '16 at 13:19
does the very bottom bit of the answer to [this question](http://stackoverflow.com/questions/16611904/ignorecase-in-criteria-builder-in-jpa) help at all? — Boneist, Jun 01 '16 at 13:28
Yep got it working, thank you. this is the order Expression to use builder.function("nlssort", String.class, yourColumnExpression, builder.literal("nls_sort=binary")); — TheBakker, Jun 01 '16 at 14:21

Weird Oracle orderBy behavior on String with * and (

1 Answers1