18 decembrie 2011

Domnule Oracle, vorbiți românește?


În România există în vigoare "Legea 500/2004 privind folosirea limbii române în locuri, relații și instituții publice". Este vorba de faimoasa (la vremea ei) "Lege Pruteanu". În principiu, ea își propune să impună o corectă utilizare a limbii române în spațiul public, însă aplicarea ei, din punctul meu de vedere, este o mare problemă, chiar dacă sunt prevăzute amenzi "grase", care pot ajunge până la 5000 RON! Nu sunt jurist, prin urmare s-ar putea să nu am dreptate, dar mă gândesc că dacă ar fi de dezvoltat o aplicație pentru o instituție publică, atunci anumite considerente ce țin de folosirea corectă a limbii române ar trebui luate în considerare, mai ales dacă publicul larg ar fi implicat. Spre exemplu, o aplicație de tip "infochioșc" sau una de plată online a impozitelor ar putea fi vizate de legea în cauză. Așadar, haideți să presupunem că o astfel de aplicație ar utiliza o bază de date Oracle și să vedem cum ar trebui ea configurată astfel încât să obținem o operabilitate cât mai bună în contextul limbii române.

Setul de caractere al bazei de date

Primul pas ar fi să instalăm o nouă bază de date. La un moment dat, în procesul de instalare, vom fi întrebați ce set de caractere dorim să alegem pentru această bază de date. Chiar așa, oare ce set de caractere ar trebui configurat ținând cont că limba româna conține, pe lângă literele latine, și cele cu diacritice? Ne interesează un set de caractere care să conțină: ăâîșț și, evident, majusculele corespunzătoare.

A existat o întreagă controversă legată de diacriticile pentru "s" și "t". Avem "şţ" scrise cu o sedilă sub literă, pe de o parte, și "șț" cu o virguliță dedesubt, pe de altă parte. Abia în 2003, Academia Română s-a pronunțat și a decis că varianta corectă este cea cu viguliță, adică: ȘșȚț.

În regula! Revenind la problema noastră, cea de a alege setul de caractere adecvat limbii române, ne-ar trebui varianta care să suporte diacriticele cu virguliță. Dacă baza de date va fi folosită doar în contextul limbii române, un set de caractere regional pe 8 biți ar putea fi o opțiune. Standardul ISO/IEC 8859-16 ar fi cel mai potrivit din acest punct de vedere, dat fiind că are definite caracterele cu diacriticile corecte și, în plus, stilul românesc de folosire a ghilimelelor („” sau «»). Dar, surpriză! Acest set de caractere nu este disponibil în Oracle. Singurele seturi de caractere pe 8 biți care se apropie cât de cât de varianta românească ar fi: EE8MSWIN1250 și EE8ISO8859P2, dar acestea conțin diacriticele cu sedilă, deci cele incorecte.

Asta înseamnă că ne rămâne o singură opțiune pentru configurarea setului de caractere, și anume o variană Unicode, cel mai probabil AL32UTF8. Secțiunea "Latin Extended-B" din codificarea Unicode furnizează diacriticele corecte.
Figura 1 - Codificarea Unicode pentru caracterele cu diacriticele românești corecte

O primă consecință ar fi că aplicația pe care o dezvoltăm va trebui să țină cont, cel puțin la nivelul bazei de date, de toate aspectele ce se referă la utilizarea unui set de caractere "multi-byte".

Configurația pe client

Am stabilit, baza de date este Unicode, consecință a faptului că nu am găsit un set de caractere corespunzător în Oracle, care să ne ofere reprezentările corecte ale lui "ȘșȚț". Asta înseamnă că aplicația client trebuie să poată opera cu Unicode, astfel încât să poată manipula corect aceste caractere. Dat fiind că Oracle nu oferă ca set de caractere varianta ISO-8859-16, nu ne putem baza pe nici o conversie ce ar putea fi efectuată între Unicode, setul de caractere al bazei de date și un posibil ISO-8859-16 (în cazul în care ar exista), setul de caractere al clientului.

Prin urmare, pe client, valoarea variabilei NLS_LANG ar trebui să fie ROMANIAN_ROMANIA.AL32UTF8, dar numai dacă aplicația suportă Unicode. În Windows, de pildă, SqlPlus nu are habar de Unicode, prin urmare o astfel de configurație nu funcționează. Pentru mine, un utilizator fidel de SqlPlus, asta este o mare problemă. Din fericire, îmi petrec puțin timp folosind sistemul de operare Windows, iar versiunea SqlPlus de *NIX este compatibilă Unicode. Oricum, în Windows putem utiliza SqlDeveloper care este o aplicație Java și știe cu ce se mănâncă Unicode-ul.

Faptul că am specificat în definiția NLS_LANG limba și teritoriul specifice României ne aduce la pachet câteva beneficii, deloc de neglijat. În primul rând, avem parte de denumirile românești ale zilelor și lunilor din reprezentarea datelor calendaristice, convenții de afișare a acestor date, moneda națională și altele.
SQL> select to_char(sysdate, 'dd Month yyyy') from dual;

TO_CHAR(SYSDATE,'DDMONTHYYYY')
------------------------------
19 Decembrie  2011            
În listingul de mai sus se poate observa denumirea românească a lunii decembrie. Putem afla ce alte valori implicite sunt folosite la nivelul sesiunii ca urmare a configurării date de NLS_LANG (și nu numai), utilizând interogarea de mai jos:
SQL> select * from nls_session_parameters;

PARAMETER               VALUE
----------------------- ----------------------------
NLS_LANGUAGE            ROMANIAN                    
NLS_TERRITORY           ROMANIA                     
NLS_CURRENCY            LEI                         
NLS_ISO_CURRENCY        ROMANIA                     
NLS_NUMERIC_CHARACTERS  ,.                          
NLS_CALENDAR            GREGORIAN                   
NLS_DATE_FORMAT         DD-MM-RRRR                  
NLS_DATE_LANGUAGE       ROMANIAN                    
NLS_SORT                ROMANIAN                    
NLS_TIME_FORMAT         HH24:MI:SSXFF               
NLS_TIMESTAMP_FORMAT    DD-MM-RRRR HH24:MI:SSXFF    
NLS_TIME_TZ_FORMAT      HH24:MI:SSXFF TZR           
NLS_TIMESTAMP_TZ_FORMAT DD-MM-RRRR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY       LEI                         
NLS_COMP                BINARY                      
NLS_LENGTH_SEMANTICS    BYTE                        
NLS_NCHAR_CONV_EXCP     FALSE 

17 înregistrări selectate.  

Mesaje server în limba română

În listing-ul de mai sus se poate observa că inclusiv textul de la finalul interogării este tradus în românește. Dacă acest lucru nu se întâmplă, atunci înseamnă că fișierul cu traducerile în limba română nu a fost instalat. Dacă doriți această funcționalitate, acest fișier poate fi instalat ulterior cu ajutorul bine-cunoscutului "OUI - Oracle Universal Installer'.

Dacă mă întrebați pe mine, vă spun că nu-mi place deloc să văd mesaje de eroare sau texte informative provenind de la serverul Oracle, afișate în limba română. Am cel puțin două motive pentru care consider respectivele traduceri inutile. În primul rând, nu sunt în favoarea afișării de mesaje de eroare așa cum sunt ele "aruncate" de către server, cel puțin nu în aplicațiile folosite de operatori obișnuiți, cei fără mari cunoștințe tehnice. Pornind de la codul de erorare, aplicația trebuie să fie suficient de "deșteaptă" să afișeze un mesaj prietenos utilizatorului. Spre exemplu, în loc de "ORA-01536 - space quota exceeded for tablespace ..." aplicația poate afișa bine-mersi: "Ce faci Costeluș? Ne mănânci tot spațiul pe disc?". Am exagerat, dar ați înțeles idea. În cazul unei erori netratate, se poate afișa pur și simplu un mesaj generic, cum ar fi cel de mai jos:


Bine-înțeles, din respect pentru utilizatorul necunoscător al limbii engleze, va trebui să traduceți mesajul de mai sus în limba română. Eroarea efectivă ar trebui consemnată într-un fișier de tip log, iar, în acest caz, traducerea acesteia nu este necesară.

Un alt motiv pentru care nu-mi plac textele în română returnate de serverul Oracle este că nu am reușit să le fac să fie afișate corect. Las de-o parte faptul că ele sună stupid traduse (spre exemplu "view" este tradus ca "vizualizare"), însă mesajele care conțin diacritice se comportă chiar ciudat la afișare. Spre exemplu:
~ $ export NLS_LANG=ROMANIAN_ROMANIA.AL32UTF8
~ $ sqlplus talek/***

SQL*Plus: Release 11.2.0.2.0 Production on L Dec 19 17:34:23 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Conectat la:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> a
SP2-0004: Nimic de adăugat.
SQL> select * from a;
select * from a
              *
EROARE la linia 1:
ORA-00942: tabelul sau vizualizarea nu exist¿ 
Se poate observa că în cazul primului mesaj de eroare caracterul "ă" este afișat fără probleme. În cel de-al doilea caz, ghinion! Oracle a uitat cum să-l afișeze și pune în loc un stupid "¿", cum că, vezi Doamne, nu are caracterul corespunzător în setul de caractere client. Ținând cont că pe client am configurat AL32UTF8 (folosesc Linux), a nu găsi corespondent în Unicode mi se pare ciudat!

Considerente privind sortarea

Sortarea reprezintă o operație uzuală în majoritatea aplicațiilor. Totuși, atunci când aplicația operează în contextul unei anumite limbi, așa cum este româna, sortarea textelor trebuie să țină cont de regulile lingvistice impuse de respectiva limbă.

Tipuri de sortări

Oracle operează cu trei tipuri de sortări: binară, mono-lingvistică și multi-lingvistică. El poate fi configurat a folosi una din cele trei metode prin intermediul parametrului NLS_SORT. Acesta poate fi inițializat, fie sub forma unei variabile de sistem pe client, fie la nivelul sesiunii, prin intermediul unei comenzi "ALTER SESSION SET NLS_SORT...".

Sortarea binară este cea mai rapidă și se bazează pe codurile asociate fiecărui caracter. Dat fiind că în ASCII, dar și în EBCDIC, caracterele au asociate coduri de la "A" la "Z" în ordine crescătoare, acest tip de sortare se dovedește a fi extrem de utilă, mai ales pentru limbile care folosesc ASCII standard. În alte limbi, acest tip de sortare s-ar putea să nu fie cel potrivit. Spre exemplu, în română avem litera "ă" care, conform alfabetului românesc, urmează după litera "a", însă conform codificării Unicode, "ă" este poziționat mult după "z". Prin urmare, o sortare binară nu produce rezultatul așteptat pentru limba română. Să exemplificăm:
SQL> create table cuvinte (
  2    text varchar2(100) 
  3  );
  
Table created.

SQL> insert into cuvinte values ('asta');

1 row created.

SQL> insert into cuvinte values ('ăsta');

1 row created.

SQL> insert into cuvinte values ('ista');

1 row created.

SQL> alter session set nls_sort='BINARY';

Session altered.

SQL> select * from cuvinte order by 1;

TEXT
----
asta
ista
ăsta

SQL> alter session set nls_sort='ROMANIAN';

Session altered.

SQL> select * from cuvinte order by 1;

TEXT
----
asta
ăsta
ista 
În exemplul de mai sus, am creat o tabelă care conține trei cuvinte sinonime. Se poate observa că, în cazul sortării binare, rezultatul nu este satisfăcător pentru limba română: cuvântul "ăsta" este afișat după "ista". Soluția este trecerea pe o sortare de tip mono-lingvistic, în cazul nostru una specifică limbii române, caz în care obținem lista sortată corect. Oracle furnizează numeroase astfel de sortări mono-lingvistice, potrivite a fi folosite în contextul unei anumite limbi.

În sfârșit, există și așa-numitele sortări multi-lingvistice. Acestea operează în contextul sortării simultane de texte provenind din limbi diferite. Spre exemplu, într-o bază de date globalizată putem stoca nume de clienți de naționalități diferite. Dacă aplicația trebuie să furnizeze un raport cu acești clienți grupați după naționalitate, iar în cadrul fiecărui grup aceștia trebuie sortați după convenția specifică limbii lor, atunci o sortare de tip multi-lingvistic se poate dovedi extrem de utilă. În general, această soluție de tip "bună la toate" are, evident, limitările ei. Ea produce un rezultat rezonabil pentru toate limbile, însă nu va ține seama de tot felul de excepții specifice unei anumite limbi, excepții care sunt tratate în sortările mono-lingvistice. Haideți să exemplificăm:
 SQL> create table customers (
  2    cust_name varchar2(100),
  3    citizen varchar2(100)
  4  );
  
Table created.

SQL> insert into customers values ('Cukor Atila', 'HUNGARIAN');

1 row created.

SQL> insert into customers values ('Csak Atila', 'HUNGARIAN');

1 row created.

SQL> insert into customers values ('Țeicu Marius', 'ROMANIAN');

1 row created.

SQL> insert into customers values ('Zeicu Angela', 'ROMANIAN');

1 row created.

SQL> alter session set nls_sort='GENERIC_M';

Session altered.

SQL> select * from customers order by 2, 1;

CUST_NAME    CITIZEN
------------ ---------
Csak Atila   HUNGARIAN
Cukor Atila  HUNGARIAN
Țeicu Marius ROMANIAN 
Zeicu Angela ROMANIAN 
În exemplul de mai sus am creat o tabelă cu clienți de naționalitate română și maghiară, deci avem de-a face cu două seturi distincte de convenții de sortare, motiv pentru care am folosit un criteriu de sortare multi-lingvistic: GENERIC_M. În Oracle, toate aceste criterii multi-lingvistice sunt sufixate cu "_M". Se observă că am obținut un rezultat rezonabil pentru ambele limbi. Totuși, așa cum spuneam, aspectele de finețe sunt lăsate de-o parte. Spre exemplu, un vorbitor de maghiară ar putea aprecia ca greșită sortarea de mai sus, cea corectă fiind următoarea:
 SQL> alter session set nls_sort='XHUNGARIAN';

Session altered.

SQL> select * from customers where citizen='HUNGARIAN' order by 2, 1;

CUST_NAME   CITIZEN  
----------- ---------
Cukor Atila HUNGARIAN
Csak Atila  HUNGARIAN 
Conform rigorii gramaticale a limbii maghiare, cică "Cs" vine după "C", prin urmare sortarea corectă ar fi cea de mai sus.

Modificatori de sortare

Fiecare tip de sortare specificat prin NLS_SORT acceptă doi modificatori: "case insensitive" și "accent insensitive". Cel "case insensitive" se poate activa prin adăugarea sufixului "_CI", în acest mod sortarea neținând cont de caracterele majuscule. Prin urmare, teoretic, "Popescu" și "popescu" ar putea apărea într-o ordine aleatorie, dat fiind că majuscula "P" este ignorată.

Modificatorul "accent insensitive" se poate activa prin adăugarea sufixului "_AI" și se referă la acele caractere cu diacritice care, într-o astfel de sortare, sunt asimilate caracterului de bază. Spre exemplu, "ă" și "â" sunt considerate a fi "a". Acest tip de modificator, deși nu evident, îl include și pe cel "case insensitive", prin urmare o sortare "accent insensitive", pe lângă faptul că ignoră diacriticile, nu va lua, de asemenea, în considerare nici caracterele majuscule. Figura de mai jos surprinde relația de incluziune a acestor tipuri de sortare, de la cel mai restrictiv la cel mai puțin restrictiv.
Figura 2 - Relația între tipurile de sortare cu modificatori

Funcția NLSSORT

Dacă nu dorim a ne raporta la valoarea parametrului NLS_SORT configurat la nivelul sesiunii, putem folosi bine-mersi funcția NLSSORT, direct în clauza ORDER BY a interogării. Spre exemplu:
SQL> select value from nls_session_parameters where parameter='NLS_SORT';

VALUE
------
BINARY

SQL> select * from cuvinte order by nlssort(text, 'NLS_SORT=ROMANIAN');

TEXT
----
asta
ăsta
ista 
Funcția NLSSORT primește ca prim parametru coloana (textul) de sortat și un al doilea parameteru prin care specificăm ce tip de sortare dorim a se folosi. Rezultatul acestei funcții este o valoare de tip RAW, care poartă denumirea de cheie de colaționare (din termenul englezesc "collation key").

Compararea textelor

Fratele mai mic al lui NLS_SORT este parametrul NLS_COMP. Acesta este folosit pentru a indica serverului Oracle ce tip de comparații dorim a se efectua în cazul informației textuale. Valorile valide pentru acest parametru sunt: BINARY, LINGUISTIC și ANSI. Comparațiile de tip binar folosesc codurile asociate fiecărui caracter pentru a determina în ce măsură două texte sunt similare, spre deosebire de comparațiile de tip lingvistic care vor efectua aceste operații folosind regulile lingvistice date de valoarea parametrului NLS_SORT. Limba română are mai puține anomalii care ar putea să conducă la tot felul de perplexități, dar în germană, de pildă, "ß" ar putea fi egal cu "SS". Să dăm un exemplu concret:
SQL> alter session set NLS_SORT='XGERMAN_CI';

Session altered.

SQL> alter session set NLS_COMP='LINGUISTIC';

Session altered.

SQL> select decode('ß', 'SS', 'sunt egale', 'nu sunt egale') rezultat from dual;

REZULTAT
----------
sunt egale 
Recunosc că pentru mine limba germană este o mare enigmă, dar oricine a studiat la școală această limbă vă poate spune că 'ß' devine 'SS' atunci când cuvântul trebuie scris cu majusculă. În exemplul de mai sus, am folosit o sortare specifică limbii germane de tip "case insensitiv" (vezi sufixul _CI), prin urmare, în acest context, 'ß' și 'SS' sunt egale. Astfel de comparații nu pot fi efectuate în modul binar. Rețineți, totuși, că funcțiile de tip NLS* (cum ar fi NLS_LOWER, NLS_UPPER etc.), precum și cele care folosesc expresii regulate de tipul REGEXP_LIKE, REGEXP_INSTR și altele, folosesc întotdeauna comparații lingvistice, chiar dacă NLS_COMP are valoarea BINARY.

Valoarea ANSI, care poate fi atribuită parametrului NLS_COMP, este moștenită din versiunile Oracle anterioare și nu este recomandat a se folosi. Prin urmare, nu vom descrie aici această opțiune.

Indecși lingvistici

Din punctul de vedere al optimizării interogărilor care folosesc sortări și comparații de tip lingvistic, putem utiliza ceea ce Oracle denumește un "index lingvistic". Acesta nu se deosebește cu nimic față de un index funcțional (function based index), doar că folosește funcția NLSSORT. În orice caz, un astfel de index ar putea îmbunătăți semnificativ performanța anumitor interogări. Iată, mai jos un exemplu:
SQL> create table dictionar (
  2    cuvant char(2000) not null
  3  );
  
Table created.

SQL> insert into dictionar select dbms_random.string('P', 100) from dual connect by level <= 10000;

10000 rows created.

SQL> insert into dictionar values ('România');

1 row created.

SQL> create index ix_dictionar_cuvant on dictionar(cuvant);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'dictionar', estimate_percent => 100, cascade => true);

PL/SQL procedure successfully completed.                                                                                                                       

SQL> alter session set NLS_SORT='ROMANIAN';

Session altered.

SQL> set timing on

SQL> select * from dictionar where cuvant = 'România';

CUVANT
-------
România

Elapsed: 00:00:06.85 
Avem un dicționar cu 10001 cuvinte. Unul din cuvinte este "România". L-am adăugat separat, tocmai pentru a-l regăsi mai ușor ulterior. Avem creat frumușel și un index, mizând pe o optimizare a căutărilor în dicționar. Totuși, se poate observa că o astfel de căutare în contextul limbii române durează aproape șapte secunde. Să vedem planul de execuție:
SQL_ID  2q8n0pp7pnvd0, child number 1
-------------------------------------
select * from dictionar where cuvant = 'România'

Plan hash value: 3806125064

------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                     |      1 |        |      1 |00:00:06.76 |    3353 |
|*  1 |  INDEX FAST FULL SCAN| IX_DICTIONAR_CUVANT |      1 |    100 |      1 |00:00:06.76 |    3353 |
------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
                                                                                                                                                               
   1 - SEL$1 / DICTIONAR@SEL$1

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

   1 - filter(NLSSORT(INTERNAL_FUNCTION("CUVANT"),'nls_sort=''ROMANIAN''')=HEXTORAW('645A50175
              53C14000201010101010100') ) 
Index-ul este folosit, dar este parcurs în întregime, prin urmare nu avem cum să beneficiem de cine știe ce câștig de performanță. În orice caz, se poate observa în listingul de mai sus, faptul că Oracle a adăugat de la sine-putere, o clauză de filtrare ce foloște funcția NLSSORT, prin urmare folosirea index-ului nu se poate realiza decât în modul "full scan".

Acesta este un caz tipic în care un index lingvistic ne vine în ajutor. Haideți să creăm unul și să vedem ce se întâmplă:
SQL> create index ix_dictionar_cuvant_ro on dictionar(nlssort(cuvant, 'NLS_SORT=ROMANIAN'));

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'dictionar', estimate_percent => 100, cascade => true);

PL/SQL procedure successfully completed.

SQL> select * from dictionar where cuvant = 'România';

CUVANT
-------
România

Elapsed: 00:00:00.01 
De această dată, aceeași interogare a durat sub o secundă, datorită folosirii indexului lingvistic. Planul de execuție, în acest caz, este următorul:
SQL_ID  2q8n0pp7pnvd0, child number 1
-------------------------------------
select * from dictionar where cuvant = 'România'

Plan hash value: 2950978053

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DICTIONAR              |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | IX_DICTIONAR_CUVANT_RO |      1 |      1 |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DICTIONAR@SEL$1
   2 - SEL$1 / DICTIONAR@SEL$1

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

   2 - access("DICTIONAR"."SYS_NC00002$"=HEXTORAW('645A5017553C14000201010101010100') ) 
Atenție, anumite funcții SQL nu știu să se folosească de acest tip de index. În Oracle 11g, acestea sunt:
  • INSTR (INSTR, INSTRB, INSTR2, INSTR4, INSTRC)
  • MAX
  • MIN
  • REPLACE
  • TRIM
  • LTRIM
  • RTRIM
  • TRANSLATE

Probleme Gogule?

Sunt câteva aspecte legate de utilizarea limbii române în bazele de date Oracle, destul de enervante. Este ca și când băieții de la Oracle nu și-au bătut prea tare capu' cu setările pentru limba română, iar asta lovește direct în mândria mea de român, patriot convins.

Comparațiile "accent-insensitive"

Numele meu de familie este "Tică". Ori de câte ori ma duc să plătesc vreo factură, tanti de la ghișeu mă întreabă politicos (sau nu) cum mă cheamă, tastează numele, după care mă întreabă dacă stau la adresa "cutare". Deformația profesională mă îndeamnă la a-mi imagina cum este dezvoltat sistemul folosit de tăntică. Oare numele meu este stocat în baza de date cu diacritice? Așa ar trebui, că doar în buletin figurez cu numele "Tică" și nu "Tica". Dacă numele este stocat cu diacritice, atunci, mă întreb, oare tanti caută în baza de date folosind diacritice? Eu, unul, mă îndoiesc! Mai mult ca sigur, tanti introduce de la tastatură un simplu "tica"!

Bun, avem un proces de aplicație, numai bun de modelat. Cum putem căuta în baza de date făcând abstracție de majuscule și diacritice? Păi, ne amintim că putem folosi modificatorul "_AI", adică "accent-insensitive". Răspunsul, în acest caz, ar fi NLS_SORT=ROMANIAN_AI. Să-l vedem la treabă:
 SQL> create table clienti (
  2    nume varchar2(100),
  3    adresa varchar2(1000)
  4  );
  
Table created.

SQL> insert into clienti values ('Tică', 'Voievozilor 16');

1 row created.

SQL> insert into clienti values ('Țică', 'Nicolae Iorga 20');

1 row created.

SQL> insert into clienti values ('Tica', 'Basarbai 15');

1 row created.

SQL> insert into clienti values ('Ticu', 'Zorilor 17');

1 row created.

SQL> alter session set NLS_SORT='ROMANIAN_AI';

Session altered.

SQL> alter session set NLS_COMP='LINGUISTIC';

Session altered.


SQL> select * from clienti where nume = 'tica';

NUME ADRESA
---- -----------
Tica Basarbai 15 
Se poate observa că, deși am configurat modul "accent-insensitive", diacriticile nu sunt asimilate caracterului de bază corespunzător. Din fericire, criteriul "case-insensitive" este respectat. Păi, să nu ne supărăm pe simpaticii de la Oracle pentru că au lăsat treaba neterminată?

Soluția de moment ar putea fi utilizarea modului multi-lingvistic "GENERIC_M_AI" care, în acest caz, funcționează bine pentru limba română.
 SQL> alter session set NLS_SORT='GENERIC_M_AI';

Session altered.

SQL> select * from clienti where nume = 'tica';

NUME ADRESA          
---- ----------------
Tică Voievozilor 16  
Țică Nicolae Iorga 20
Tica Basarbai 15     
Eeee, așa da! Folosirea modului GENERIC_M poate crea însă, alte probleme. Spre exemplu, dacă pentru a căuta folosim în loc de "tica" textul "t#i@c!a", obținem același rezultat.
SQL> select * from clienti where nume = 't#i@c!a';

NUME ADRESA          
---- ----------------
Tică Voievozilor 16  
Țică Nicolae Iorga 20
Tica Basarbai 15 
Acest lucru se întâmplă deoarece în modul GENERIC_M, semnele de punctuație, precum și alte caractere speciale sunt ignorate. Pe de altă parte, dacă stau și mă gândesc bine, în cazul exemplificat de noi, cel cu tanti operatoarea, ce părea inițial o problemă, se poate dovedi, în final, un avantaj. Dacă tanti are sindromul "fat-fingers" sau nu e foarte atentă la tastat și mai strecoară câte un semn de exclamare sau alte astfel de caractere, operația de căutare va funcționa foarte bine. Nu mă aștept să existe nume de persoane care să conțină semne de punctuație, deși poate mi-ar plăcea să mă cheme "Tică! Alexandru".

Expresii regulate

Sunt câteva aspecte interesante legate de folosirea expresiilor regulate în contextul unei limbi date. Așa cum am precizat deja, indiferent de valoarea parametrului NLS_COMP, funcțiile REGEXP_* din Oracle se raportează la valoarea dată de NLS_SORT. Asta înseamnă că ele sunt suficient de "deștepte" pentru a ști că, în română de pildă, criteriul "[a-b]" se referă de fapt la litera "a", "ă", "â" și "b".

Mai mult, aceste funcții au implementată funcționalitatea de "clasă de caractere echivalente". Sintaxa este "[= =]". În limba română, [=a=] ar putea fi folosit pentru a recunoaște atât litera "a", cât și "ă" și "â". Ne amintim de exemplul precedent cu stimabila de la ghișeu și ne dăm seama că am putea folosi o soluție bazată pe expresii regulate:
SQL> alter session set NLS_SORT='ROMANIAN_AI';

Session altered.

SQL> select * from clienti where regexp_like(nume, '[[=t=]]ic[[=a=]]');

NUME ADRESA
---- -----------
Tica Basarbai 15 
Nu funcționează! Am spus eu că și-au bătut joc ăștia de limba română. Oare ar putea fi amendați? Glumesc! Haideți să încercăm cu un NLS_SORT inițializat cu BINARY.
SQL> alter session set NLS_SORT='BINARY';

Session altered.

SQL> select * from clienti where regexp_like(nume, '[[=t=]]ic[[=a=]]');

NUME ADRESA          
---- ----------------
Tică Voievozilor 16  
Țică Nicolae Iorga 20
Tica Basarbai 15 
Na, că așa funcționează, toate numele de persoane corespunzătoare fiind selectate. Conform rezultatului obținut, s-ar părea că o clasă de echivalență folosită într-o expresie regulată include, pe lângă cele cu diacritice, și cele majuscule.

Ținând cont de faptul că funcțiile REGEXP_* au posibilitatea specificării modificatorului de "case insensitive" ca parametru adițional, iar NLS_SORT poate opera de asemena cu acest modificator, e interesant de văzut care are întâietate.
SQL> alter session set NLS_SORT='ROMANIAN';

Session altered.

SQL> select 's-a potrivit' from dual where regexp_like('Cluj', 'cluj');

no rows selected

SQL> alter session set NLS_SORT='ROMANIAN_CI';

Session altered.

SQL> select 's-a potrivit' from dual where regexp_like('Cluj', 'cluj');

no rows selected

SQL> select 's-a potrivit' rezultat from dual where regexp_like('Cluj', 'cluj', 'i');

REZULTAT    
------------
s-a potrivit
S-ar părea că, funcțiile REGEXP_* nu țin cont de modificatorul setat prin intermediul parametrului NLS_SORT, ci pe cel specificat explicit la invocarea funcției (vezi parametrul 'i' din ultimul apel).

Atenție, "long vehicle"

Funcția NLSSORT este utilizată ori de câte ori intervin sortări lingvistice. Așa cum am precizat anterior, această funcție preia un text și, în funcție de setările de limbă pentru sesiunea curentă, returnează o valoarea de tip RAW, ce este mai departe utilizată în operația de sortare. Dimensiunea maximă pe care NLSSORT o poate returna este de 2000 de bytes. Asta înseamnă că, furnizând un text cu o lungime mare, se poate ajunge la depășirea limitei de 2000 de bytes, caz în care, valoarea de tip RAW este calculată doar pentru prima parte a textului, adică doar pentru prefix. Lungimea acestui prefix diferă în funcție de tipul de sortare. Conform documentației, pentru cel mono-lingvistic prefixul este compus din primele 1000 de caractere, iar pentru cel multi-lingvistic, primele 500 de caractere sunt luate în considerare. Totuși, aceste lungimi nu sunt chiar bătute în cuie, așa cum arată și testele de mai jos. În exemplele noastre, dimensiunea prefixului pentru sortările de tip mono-lingvistic a fost de 999 de caractere, iar pentru cele de tip multi-lingvistic de 498.
SQL> create table cobai (
  2    text varchar2(4000)
  3  );

Table created.

SQL> insert into cobai values (rpad('a', 999, 'a') || 'c');

1 row created.

SQL> insert into cobai values (rpad('a', 999, 'a') || 'b');

1 row created.

SQL> select count(distinct nlssort(text, 'NLS_SORT=ROMANIAN')) from cobai order by text;

COUNT(DISTINCTNLSSORT(TEXT,'NLS_SORT=ROMANIAN'))
------------------------------------------------
                                               1 
În tabela "COBAI" am inserat două texte diferite, dar cu un prefix comun format din 999 de "a"-uri. Când comparăm rezultatul returnat de NLSSORT pentru cele două texte diferite, constatăm că obținem aceeași valoare. Asta înseamnă că nu ne putem baza pe o sortare 100% corectă pentru astfel de texte cu lungimi mari.

În ceea ce privește sortările multi-lingvistice, lucrurile se comportă diferit. Pentru texte de lungimi mai mari de 498 de caractere obținem chiar o eroare, fapt ce eu, unul, nu l-am văzut consemnat în documentația Oracle:
 
SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 09:36:06 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Connected.

SQL> create table cobai (
  2    text varchar2(4000)
  3  );
  
Table created.

SQL>
SQL> insert into cobai values (rpad('a', 498, 'a') || 'c');

1 row created.

SQL> insert into cobai values (rpad('a', 498, 'a') || 'b');

1 row created.

SQL> select count(distinct nlssort(text, 'NLS_SORT=GENERIC_M')) from cobai order by text;
select count(distinct nlssort(text, 'NLS_SORT=GENERIC_M')) from cobai order by text 
                      *
ERROR at line 1:      
ORA-00910: specified length too long for its datatype 
Din punctul meu de vedere, faptul că funcția NLSSORT aruncă o eroare în astfel de cazuri trebuie să ne îndemne la precauție înainte de a seta parametrul NLS_SORT pe un tip de sortare multi-lingvistic, dat fiind că anumite interogări care sortează după coloane cu texte de lungime mare, s-ar putea să nu mai funcționeze și să ne întâmpine sec cu eroarea de mai sus.

Interesant e că, pe o bază de date Oracle Express, chiar și pentru o sortare mono-lingvistică obținem o eroare, e adevărat, diferită. Spre exemplu:
SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 22 09:36:06 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Beta

Connected.

SQL> create table cobai (
  2    text varchar2(4000)
  3  );

Table created.
  
SQL> insert into cobai values (rpad('a', 999, 'a') || 'c');

1 row created.

SQL> insert into cobai values (rpad('a', 999, 'a') || 'b');

1 row created.

SQL> alter session set NLS_SORT=ROMANIAN;

Session altered.

SQL> select * from cobai order by text;
select * from cobai order by text
              *
ERROR at line 1:
ORA-01401: inserted value too large for column 
S-ar părea că pentru o bază de date Oracle Express trebuie să acordăm o atenție și mai mare coloanelor text de lungime mare, coloane folosite în contextul sortărilor lingvistice.

In extremis, putem face și asta

E clar, varianta furnizată implicit de Oracle pentru sortările în limba română nu funcționează așa cum ar trebui, în special în cazul diacriticelor. Nici valoarea pentru NLS_CURRENCY nu este cea corectă. Implicit e setată pe LEI, în loc de RON. O actualizare, zic eu, s-ar impune. Oricum, în astfel de cazuri, putem să intervenim și noi și să facem corecțiile de rigoare.

Vă prezentăm "Locale Builder", o aplicație oferită de Oracle pentru a configura toate aspectele ce țin de limbă, teritoriu, sortări și seturi de caractere. Putem, de asemanea, să inspectăm și configurațiile existente pentru a ne da seama, de pildă, motivul pentru care o modalitate de sortare nu funcționează așa cum ne-am aștepta. Haideți să vedem care-i problema cu diacriticele în limba română. Deschidem frumușel acest "lbuilder", mergem pe "File", "Open" și alegem "By object name...". Din fereastra de dialog alegem din secțiunea "Linguistic Sort(ID)", valoarea "ROMANIAN(40)". Apoi selectăm secțiunea "Base Letter" și ne uităm la modul în care sunt definite corespondențele între caractere.
Figura 3 - Corespondențe implicite pentru diacritice
Se poate observa că acestea sunt definite aiurea. În accepțiunea lor, caracterul de bază pentru "ț" este "ţ". Păi nu ar trebui să fie "t"? Eu zic că da. Aceeași problemă o regăsim și pentru celelalte litere românești cu diacritice.

Soluția este simplă! Facem corecțiile de rigoare și salvăm modificările sub forma unui nou tip de sortare pe care il vom denumi elegant "XROMANIAN". Automat va fi generat un nou identificator, iar fișierul destinație va avea extensia "nlt", care în ciuda acestei extensii, este de fapt un XML deghizat. Următorul pas ar fi compilarea acestuia într-un format binar, ce va avea extensia NLB. Folosim pentru acest lucru meniul "Tools", "Generate NLB" și furnizăm directorul în care se află fișierul NLT.

Obținem la respectiva locație mai multe fișiere NLB, dat fiind că are loc inclusiv regenerarea așa-numitelor fișiere de "NLS boot". Facem o copie a originalelor și suprascriem cu cele nou generate.
$ cd /u01/app/oracle/product/11.2.0/dbhome_1/nls/data/
$ cp -p lx0boot.nlb lx0boot.nlb.orig
$ cp -p lx1boot.nlb lx1boot.nlb.orig
$ cp /home/oracle/*.nlb /u01/app/oracle/product/11.2.0/dbhome_1/nls/data/
E aproape gata! Mai este necesar doar un "restart" al bazei de date. Haideți să vedem ce am obținut:
SQL> select * from v$nls_valid_values where parameter='SORT' and value like '%ROMANIAN%';

PARAMETER VALUE     ISDEPRECATED
--------- --------- ------------
SORT      ROMANIAN  FALSE       
SORT      XROMANIAN FALSE       
Avem un nou tip de sortare, XROMANIAN. Să vedem acum dacă am rezolvat problema diacriticelor românești:
SQL> alter session set NLS_SORT=XROMANIAN_AI;

Session altered.

SQL> alter session set NLS_COMP='LINGUISTIC';

Session altered.

SQL> select * from clienti where nume = 'tica';

NUME ADRESA
---- ----------------
Tică Voievozilor 16  
Țică Nicolae Iorga 20
Tica Basarbai 15     
Funcționează! Ne putem declara victorioși. Principalul neajuns al acestei metode este faptul că aplicația noastră ar trebui livrată împreună cu aceste fișiere NLB, iar din punctul de vedere al administrării, acest lucru complică puțin lucrurile. Dacă mediul "Oracle" este în mâinile noastre și noi suntem cei care îl administrăm, această metodă se poate dovedi salvatoare.

0 commentarii: