0

We are using NHibernate and Oracle for database access. During selection we are ordering data by text, but culture specific characters goes in the end of the list.

We want: AĀBCDEĒFG
We got: ABCDEFGĀĒ

In oracle docs we found that NLS_SORT has to be set to BINARY_AI to enable that kind of sorting.

Can you advice please if it is possible(and how) to set this parameter in NHibernate configuration or during connection initialization?

Thank you

Sergejs
  • 2,540
  • 6
  • 32
  • 51

2 Answers2

0

You can set this per session with

ALTER SESSION SET NLS_SORT=BINARY_AI;

or set it as a database parameter

You can put this in a package that can be called

PROCEDURE SET_NLS_SORT(NLS_SORT_TYPE_IN IN VARCHAR2 IS
v_sort_type VARCHAR2(100);

BEGIN

IF NLS_SORT_TYPE_IN IS NOT NULL
THEN
   v_sort_type := NLS_SORT_TYPE_IN;
ELSE
   v_sort_type := BINARY_AI;
END IF;

execute immediate  'ALTER SESSION SET NLS_SORT='||v_sort_type ;

END;

Keep in mind that using this will affect the execution plan of queries and creating a custom index may be required.

kevinskio
  • 4,431
  • 1
  • 22
  • 36
0

If you don't want to/can't set the nls_sort parameter, there's also the NLSSORT function.

Something like:

with sample_data as (select 'A' col1 from dual union all
                     select 'Ā' col1 from dual union all
                     select 'B' col1 from dual union all
                     select 'C' col1 from dual union all
                     select 'D' col1 from dual union all
                     select 'E' col1 from dual union all
                     select 'Ē' col1 from dual union all
                     select 'F' col1 from dual union all
                     select 'G' col1 from dual)
select *
from   sample_data
order by nlssort(col1, 'NLS_SORT=binary_ai');

COL1
----
A   
Ā   
B   
C   
D   
E   
Ē   
F   
G  
Boneist
  • 22,910
  • 1
  • 25
  • 40