Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====NLS_COMP und NLS_SORT für Linguistische Vergleiche und Case Ignore in der Oracle Datenbank =====
**10g/11g**


**Problemstellung:**\\
Im MS SQLServer erfolgt die Wildcard Suche über ein Textfeld automatisch unabhängig von der Groß- und Kleinschreibung.

**Lösung:**\\
Ein ähnliche Verhalten kann auch in der Oracle DB mit dem beiden NLS Parametern **NLS_COMP** und **NLS_SORT** erreicht werden. Dazu muss der NLS_COMP Parameter auf den Wert **LINGUISTIC** gesetzt werden. 

Je nach dem Parameter ob nun der Parameter NLS_SORT auf einen Wert mit <SPRACHE>**_CI**  oder <SPRACHE>**_AI** gesetzt ist, wird dann (**und nur dann!**) ein "linguistisch" Case in sensitiver  und/oder Accent in sensitiver Vergleich durchgeführt. (siehe Tabelle nach dem SQL Beispiel)


**Beispiel Einstellen der Session**:

<code sql>

-- Aktuelle Einstellung überprüfen
column value format a20
column parameter format a30
select parameter,value from nls_session_parameters where parameter like '%O%' ;
PARAMETER                      VALUE
------------------------------ --------------------
NLS_SORT                       BINARY
NLS_COMP                       GERMAN
....


-- Werte einstellen
alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=XGERMAN_AI;

-- Erneut prüfen 

select parameter,value from nls_session_parameters where parameter like '%O%' ;
PARAMETER                      VALUE
------------------------------ --------------------
NLS_SORT                       XGERMAN_CI
NLS_COMP                       LINGUISTIC

</code>

<note important>Die NLS Settings werden immer vom Client an die Datenbank Session vererbt! Zwar können die Einstellung auch DB global gesetzt werden, der Client (Java, .NEt, SQL*Plus) kann das aber jederzeit wieder überschreiben!</note>


**Test:**

<code sql>

-- Testdaten
create table t (wert varchar2(32));
insert into t values ('Gunther');
commit;

--

select * from t where wert = 'gunther';

Wert
-----
Gunther


</code>


==== Zusammenhang NLS_COMP und NLS_SORT =====

Der Default für NLS_COMP ist *binary*, d.h. keine linguistischer Vergleich.\\

Steht der Wert auf LINGUISTIC (ANSI alternativ nur für Rückwärtskompatibilität!)  ist je nach eingestelltem Wert für NLS_SORT das folgende Verhalten zu beobachteten:


^NLS_COMP =  ^ LINGUISTIC ^ ^  ^  ^  ^
^NLS_SORT ^Beschreibung ^ Vergleich \\ 'ABC'= 'abc' ^Vergleich \\ 'Pèr'= 'per' ^ > Vergleich \\ 'Z'>'A' ^ Sortierreihenfolge von \\ 1 2 3 a b c ö ü ä _ ! A B C ß^
|BINARY       |Binärer Vergleich auf den ASCII Wert     | false | false | false | ! 1 2 3 A B C _ a b c ß ä ö ü |
|GERMAN       |Deutsche Sortierung                      | false | false | true  | ! _ a A ä b B c C ö ß ü 1 2 3  |
|XGERMAN      |Deutsche Sortierung ß wird berücksichtigt! | false | false | true  | ! _ a A ä b B c C ö ß ü 1 2 3 |
|XGERMAN_DIN  |Deutsche Telefonbuch Norm                | false | false | true  | ! _ A a ä B b C c ö ß ü 1 2 3 |
|XGERMAN_CI   |_CI = postfixes für case-insensitive     | true  | false| false |! _ a A ä b B C c ö ß ü 1 2 3  |
|XGERMAN_AI   |_AI = postfixes für accent-insensitive  | true  | true  | true  | ! _ ä a A b B C c ö ß ü 1 2 3 |
|GENERIC_M_CI |Multilingual nach  [[http://www.iso.org/iso/home/store/catalogue_ics/catalogue_detail_ics.htm?csnumber=57976|ISO/IEC 14651:2011]]  \\ case-insensitive                                   | true  | false| true  | _ ! 1 2 3 a A ä b B C c ö ß ü  |
|GENERIC_M_AI | Multilingual nach  [[http://www.iso.org/iso/home/store/catalogue_ics/catalogue_detail_ics.htm?csnumber=57976|ISO/IEC 14651:2011]] \\ accent-insensitive                                    | true  | true| true | 1 2 3 A a ä b B C c ö ß ü _ ! |
|FRENCH_M_AI  | Französisch                    |true  | true  | true | 1 2 3 A a ä b B C c ö ß ü _ !   |


Die Kürzel im Detail:

  * Ohne ein  **_M**   => Monolingual Linguistic Sorts
  * mit einem **_M**   => Multilingual LInguistic Sorts
  * Postfix   **_CI**  => case-insensitive 
  * Postfix   **_AI**  => accent-insensitive und case-insensitive

siehe auch : http://docs.oracle.com/cd/E11882_01/server.112/e10729/applocaledata.htm#NLSPG593


===== Probleme =====

Wird mit den "linguistischen" Einstellungen über NLS_COMP gearbeitet, wird die Where Bedingung des SQL Statements umgeschrieben und auf beiden Seiten des Vergleiches Funktionen eingesetzt.

Beispiel:
<code sql>
SYS@GPI-WORKGROUP\JUPITER>select * from t where wert = 'pippèrr';

WERT
----------
Pippèrr


Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NLSSORT("WERT",'nls_sort=''FRENCH_M_AI''')=HEXTORAW('
              023F021B023F023F024F02450245') )
              
</code>

Beachten Sie die Filter Bedingung:\\
Aus einem "harmlosen" **wert = 'pippèrr'** \\
wird ein **(NLSSORT("WERT",nls_sort=FRENCH_M_AI)=HEXTORAW('023F021B023F023F024F02450245') )** !

Damit wird kein Index mehr verwendet!


Alternativ kann auch ein [[http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch5lingsort.htm#NLSPG294|linguistischer Index]] angelegt werden:

<code sql>

CREATE INDEX idx_t ON t (NLSSORT(wert, 'NLS_SORT=FRENCH_M_AI'));


select * from t where wert = 'pippèrr';

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     2 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |     2 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("WERT",'nls_sort=''FRENCH_M_AI''')=HEXTORAW('023F021B023F023F024F02450245') )

</code>

=====  NLSSORT =====

Mit der Funktion NLSSORT im order by kann auch die entschrechende Reichenfolge erzwungen werden (unabhängig von den NLS Settings in der Session!):

<code sql>
SQL> select * from t order by nlssort(wert,'NLS_SORT=BINARY')

WERT           
---------------
! 1 2 3 A B C _ a b c ß ä ö ü 

15 rows selected.

SQL> select * from t order by nlssort(wert,'NLS_SORT=XGERMAN_DIN')

WERT           
---------------
! _ A a ä B b C c ö ß ü 1 2 3 

15 rows selected.

SQL> select * from t order by nlssort(wert,'NLS_SORT=XGERMAN_AI')

WERT           
---------------
! _ ä a A b B C c ö ß ü 1 2 3 

15 rows selected.

SQL> select * from t order by nlssort(wert,'NLS_SORT=GENERIC_M_CI')

WERT           
---------------
_ ! 1 2 3 a A ä b B C c ö ß ü 

15 rows selected.


</code>

(Werte zur Lesbarkeit nach rechts geschrieben .-) !)


===== Das ß in UPPER Vergleichen =====

Soll das ß in einem UPPER zu einem doppel s umgewandelt werden muss als NLS Setting XGERMAN für [[http://oracle.informatik.haw-hamburg.de/server.111/b28286/functions105.htm|NLS_UPPER]] eingesetzt werden!


Beispiel:

**ß wird nicht gewandelt**

<code sql>

select *  from DUAL where UPPER ('größe') = 'GRÖSSE';

no rows selected.


select UPPER ('größe') from DUAL

UPPER('GRÖßE')
--------------
GRÖßE      

</code>


**ß wird nicht gewandelt**

<code sql>

select *  from DUAL  where NLS_UPPER ('größe', 'NLS_SORT=GERMAN') = 'GRÖSSE';

no rows selected.


select NLS_UPPER ('größe', 'NLS_SORT=GERMAN') from DUAL


NLS_UPPER('GRÖßE','NLS_SORT=GERMAN')
------------------------------------
GRÖßE   
                            

</code>

** ß wird zu SS gewandet durch den XGERMAN NLS Parameter!  **
   
<code sql>

select *  from DUAL where NLS_UPPER ('größe', 'NLS_SORT=XGERMAN') = 'GRÖSSE'

DUMMY
-----
X    

1 row selected.


select NLS_UPPER ('größe', 'NLS_SORT=XGERMAN') from DUAL

NLS_UPPER('GRÖßE','NLS_SORT=XGERMAN')
-------------------------------------
GRÖSSE                               


</code>







====Quelle:====
  * http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams143.htm#REFRN10117
  * http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch5lingsort.htm

Support:

  * Linguistic Sorting - Frequently Asked Questions(Doc ID 227335.1)