TO_NUMBER e bug di Oracle

Sono ormai due settimane che è stata presentata ufficialemente (non rilasciata) la versione 11g di Oracle database server, sono fioccate su internet un sacco di notizie e commenti su questo lancio e su questa nuova versione, una interessante discussione si è sviluppato sullo usenet group cdos in seguito ad un post di Howard Rogers che per conto mia non dice nulla di sorprendente.

In sostanza Howar Rogers critica un po’ l’entusiasmo con cui Oracle ha lanciato la nuova “release” e soprattutto critica il rappresentante di quella che si dichiara una associazione “indipendente” (IOUG) che alla presentazione ha dichiarato che oltre un terzo dei suoi associati è pronta a passare ad Oracle 11g.

Quello che dice Howar Rogers non credo sorprenda, ma anzi sia l’opinione di qualunque “tecnico” che lavori seriamente con i database Oracle, una cosa sono i messaggi pubblicitari, un’altra cosa è la realtà. Nessuna persona responsabile, che debba garantire il funzionamento di un sistema informatico si butterebbe ad usare un nuovo prodotto con nuove funzionalità appena uscite. Almeno non io. Per fare un esempio nella mia azienda sono state fatte installazioni di Oracle 9iR2 fino a pochi mesi fa, la migrazione a 10g è stata mooolto graduale (salvo un’eccezione che per fortuna non è stata una tragedia e comunque è stata inposta dalla direzione).

Da questo post si è scatenata una discussione, oserei dire feroce, sullo usenete cdos in cui un partecipante, forse non del tutto rassegnato a come funziona il “marketing”, critica aspramente il fatto che Oracle continui a rilasciare nuove versioni del motore senza prima correggere tutti i bachi gia rilevati. Nel thread c’è un batti e ribatti piuttosto lungo fra “noons” ed altri che gli chiedono esempi concreti e non ilazioni, vista la severità delle sue critiche. Alla fine dopo ripetute insistenze pare abbia riportato due o tre esempi concreti. Francamente trovo eccessiva l’enfasi con cui noons riporta le sue idee e comunque non sono completamante d’accordo con quanto dice. Fortunatamente non mi sono mai scontrato con bachi del mototore Oracle insormontabili però è ovvio che non si può pensare che un’azienda come Oracle debba concentrare tutte le energie a correggere i bachi del prodotto e rinunciare a nuove “release”, se così facesse chiuderebbe presto. Nuno, sostenuto in questo anche da Jonathan Lewis contesta il fatto che in alcuni casi (sempre a suo dire) i bachi non siano stati corretti con singole patch ma siano stati corretti solo in patchset, costringendo il cliente ad aggiornare il database all’ultima patchset disponibile con il rischio di introdurre nuovi problemi. E’ vero che una patchset con una lunga serie di patch introduce un rischio maggiore di nuovi problemi rispetto ad una singola patch, ma anche la singola patch spesso va a toccare librerie del kernel oracle e può introdurre problemi nuovi. Insomma, siccome lavoro per un’azienda che sviluppa un “prodotto software” capisco come possa essere complesso gestire la manutenzione delle “release” e la correzione dei bachi e quindi evito di essere troppo severo su questo punto con Oracle. Bisogna ricordare che molti dei problemi segnalati sono difficilmente riproducibili, ed in questi casi trovare la causa è molto difficile.

Fra i problemi riportati nel thread uno mi ha incuriosito, si tratta del baco codificato come 2499608, registrato nel lontano agosto 2002 e riguardante la funzione TO_NUMBER

Il problema mi ha incuriosito perchè probabilmente l’ho incontrato anche io ma non me ne sono reso conto a causa della fatica che ho sempre fatto a comprendere a fondo l’utilizzo di questa funzione e dei formati numerici di Oracle. La nota metalink sul baco riporta:

” The number format model understands two types of separator specification:
1. US – ‘,’ and ‘.’
2. NLS – ‘G’ and ‘D’
The US elements require the number to be always in the US convention. The NLS
elements require the number to be in the NLS_NUMERIC_CHARACTERS convention.

The problem is that the format model element ‘,’ is treated as ‘G’ in
TO_NUMBER if ‘.’ is not also specified in the model.

The following transcript shows the problem:

SQL> alter session set nls_numeric_characters=’#%’;
Session altered.

SQL> select to_number(‘1,111′,’9,999.9’) from dual;
TO_NUMBER(‘1,111′,’9,999.9’)
—————————-
1111

SQL> select to_number(‘1,111′,’9,999’) from dual;
select to_number(‘1,111′,’9,999’) from dual
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select to_number(‘1%111′,’9,999’) from dual;
TO_NUMBER(‘1%111′,’9,999’)
————————–
1111

.

==========

NLS_NUMERIC_CHARACTERS is first set to unusual characters to better show the
problem, i.e. to avoid confusion between ‘.,’ and ‘,.’.
The first select shows that 1,111 has been correctly identified as 1111
according to US convention using format model with ‘,’ and ‘.’
The second select shows the problem: the same value could not be interpreted
after ‘.9’ has been removed from the format (although the value itself has no
decimal part).
The third select confirms the problem: the value could be interpreted after
the US group separator has been replaced with the group separator from the
NLS_NUMERIC_CHARACTERS parameter.
Note, TO_CHAR does not have this problem.

”

Nel mio caso la confusione è accentuata dal fatto che in Italia si usa il punto come separatore delle migliiaia e la virgola come separatore dei decimali. Io mi sono trovato in difficolta con l’importazione di dati esterni tramit SQLLoader.

Ho fatto delle prove e ne ho tratto due considerazioni:

1) il problema è una pippa, niente di critico, se la funzione TO_NUMBER, viene usata in modo chiaro come indicato sui manuali il problema non sussiste.

2) essendo un problema rilevato forse almeno nelle nuove release poteva essere corretto

La morale quindi è che è meglio scrivere “select to_number(‘1,234′,’9G999’) A FROM DUAL;”

piuttosto che affidarsi alla convenzione implicita dei caratteri americani (virgola per le migliaia e punto per i decimali) ” select to_number(‘1,234′,’9,999’) A FROM DUAL;”