TO_NUMBER, TO_CHAR, TO_DATE e NLS

giovedì 26 luglio 2007 alle 26:46 | Pubblicato su Diario, PL/SQL, SQL | 2 commenti

L’altro giorno, ho parlato di un baco segnalato (fra gli altri) che riguarda la funzione TO_NUMBER.

Nella mia esperienza ho riscontrato che con i tipi dato NUMBER, DATE viene fatta molta confusione e le funzioni TO_NUMBER, TO_DATE e TO_CHAR non sono ben comprese. Per quanto riguarda le date mi è stato facile capire il funzionamento delle funzioni TO_CHAR e TO_DATE e quando vanno utilizzate. Secondo me la parte difficile da capire per molti è che tali funzioni servono a far capire un formato di rappresentazione di data o di numero al database o viceversa. Cerco di spiegarmi meglio: il tipo DATE in Oracle permette di memorizzare date comprensive di anno, giorno, mese, ore, minuti e secondi. Oracle memorizza le date, come ogni altro tipo dati, come dei numeri, con una sua convenzione ben definita. Quando io scrivo ’12/07/2007 13:51:07′ non ho scritto un oggetto DATE ma una stringa, un varchar2. Se voglio salvare quella data in una colonna di tipo DATE devo usare la funzione TO_DATE per spiegare ad Oracle come ho scritto quella data, cioè qual’è l’anno, qual’è il mese eccetera eccetera. Quindi secondo la sintassi Oracle dovrò scrivere:

TO_DATE(’12/07/2007 13:51:07′,’DD/MM/YYYY HH24:MI:SS’)

Analogamente userò la funzione TO_CHAR se voglio visualizzare sul mio terminale la data in un certo modo. Attraverso la variabile NLS_DATE_FORMAT (che può essere settata come variabile d’ambiente o come parametro di sessione con il comando ALTER SESSION…) si può definire un formato predefinito ed omettere l’uso delle due funzioni, in questo caso Oracle convertirà opportunamente i formati. Nel caso di inserimento dati sconsiglio vivamente di omettere l’utilizzo della funzione TO_DATE e affidarsi alla conversione implicita.

Secondo me il caso delle date è abbastanza facile da comprendere, più difficile è il caso dei numeri. L’altro giorno sul thread del newsgroup che ha ispirato il mio primo post sull’argomento ho sottolineato che il baco evidenzia anche un uso ambiguo (perchè così permesso da Oracle) della funzione TO_NUMBER. In risposta un partecipante mi ha detto che dovendo trattare dati che arrivano dall’America, quindi numeri con la virgola come separatore delle migliaia e il punto come separatore dei decimali, e dati che arrivano dalla Francia, quindi come in Italia con la virgola come separatore dei decimali, usa sia la notazione implicita (cioè TO_NUMBER(‘1,007′,’9,999’) per dire millesette) che stabilisce che la virgola è sempre separatore delle migliaia, che quella esplicita (cioè TO_NUMBER(‘1,007′,’9G999’) dove G e D indicano rispettivamente il separatore delle migliaia ed il separatore dei decimali, definiti dal parametro NLS_NUMERIC_CHARACTERS, che se non esplicitamente settato deriva da NLS_TERRITORY che a sua volta se non esplicitamente settato deriva da NLS_LANG.

Ribadisco con forza il concetto che affidarsi a conversioni implicite è sbagliato, oggi facendo delle prove sui formati numerici mi sono ricordato che la funzione TO_NUMBER ha anche un terzo parametro opzionale che permette proprio di specificare G e D “sovrascrivendo” quelli definiti dal parametro NLS_NUMERIC_CHARACTERS. Quindi il modo corretto di gestire questa situazione è quella di usare correttamente TO_NUMBER, ad esempio


TO_NUMBER('1.000','9G999','NLS_NUMERIC_CHARACTERS='',.''')

o


TO_NUMBER('1,000','9G999','NLS_NUMERIC_CHARACTERS=''.,''')

Aggiornamento del 15/01/2007

Purtroppo mi sono accordo rileggendo questo post che la parte finale conteneva degl errori

2 commenti »

RSS feed for comments on this post. TrackBack URI

  1. però scusami non puoi scrivere tutto un post che uno legge con attenzione, e poi scrivere alla fine che ci sono degli errori senza correggerli… Beh io ci provo x’ ho proprio un problema di questo tipo nel senso che ho un numero con il . come separatore delle migliaia e la , come separ. dei decimali. Mi dà sempre to_number error maledizione!!!

  2. ho scritto “conteneva” il che significa che gli errori li avevo gia corretti. C’era però un problema con la formattazione che fa wordpress, ho quindi modificato la parte finale di codice per rendere più facile la comprensione degli esempi, che sono corretti.


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Blog su WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: