Utilisation de TO_NUMBER

Version imprimable

07/12/2012, 17h25
CinePhil

Utilisation de TO_NUMBER
Bonjour,

Nous devons importer des données d'un fichier CSV vers une table Oracle puis exécuter une procédure qui va utiliser cette table.

Dans le fichier CSV, des montants sont formatés de cette façon : "11*800,73".
Les colonnes de montants sont importés dans des colonnes VARCHAR2 dans la table.

Malheureusement, dans la procédure, le montant est affecté à une variable de type NUMBER. Je dois donc ajouter une conversion de VARCHAR2 en NUMBER(12, 2) dans la procédure pour qu'elle puisse fonctionner.

Je n'arrive pas a exprimer correctement le format à donner à la fonction TO_NUMBER, en travaillant sur un seul montant pour test.
Code:

1 2 3 SELECT TO_NUMBER(rar_mht_in, '9G999D99', 'NLS_NUMERIC_CHARACTERS= ,') AS rar_mht_in FROM envt.rar_cocw WHERE no_piece = '546'
J'obtiens cette erreur :

Citation:

ORA-12702: la chaîne de paramètre NLS n'est pas valide dans la fonction SQL

J'ai pris la syntaxe de NLS_NUMBER_CHARACTERS dans ce message.

Auparavant, d'après la doc d'Oracle, j'avais essayé ceci :
Code:

1 2 3 SELECT TO_NUMBER(rar_mht_in, '9G999D99', NLS_NUMERIC_CHARACTERS ' ,') AS rar_mht_in FROM envt.rar_cocw WHERE no_piece = '546'
Et là j'obtenais ce message :

Citation:

ORA-00907: parenthèse de droite absente

:arf:

Les montants du fichier CSV vont de l'entier simple "400" à "160 006,08".

EDIT
Si je fais cette requête :
Code:

1 2 SELECT TO_NUMBER('6 000,00', '9G999D99') FROM DUAL
J'obtiens : 6000 :ccool:

Sachant que le montant de la no_piece est justement 6 000,00, si je fais cette requête :
Code:

1 2 3 SELECT TO_NUMBER(rar_mht_in, '9G999D99') AS rar_mht_in FROM envt.rar_cocw WHERE no_piece = '546'
J'obtiens :

Citation:

ORA-01722: Nombre non valide
01722. 00000 - "invalid number"
*Cause:
*Action:

:mur: :fou: :marteau:

Plusieurs remarques :
C'est un varchar2 de combien en source ?
En spécifiant le format il vaut mieux aller à la précision maximale.
Ensuite et là ce n'est pas intuitif, la valeur du nls_numeric_caracters est une chaîne dans une chaîne, il faut donc doubler les quotes :

Code:

'NLS_NUMERIC_CHARACTERS='' .'''
Enfin, il ne faut pas intervertir le caractère des milliers et de la décimale :
Code:

1 2 3 4 5 6 7 8 select to_number('11 800,73', '999G999G999D99', 'NLS_NUMERIC_CHARACTERS='' ,''') as col from dual; -- ORA-01722: invalid number select to_number('11 800,73', '999G999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') as col from dual; COL -------- 11800.73

Citation:

Envoyé par Waldar

Plusieurs remarques :
C'est un varchar2 de combien en source ?
En spécifiant le format il vaut mieux aller à la précision maximale.

Bizarrement, celui qui a créé le machin a mis l'une des deux colonnes destinées à accueillir les montants en VARCHAR2(50 BYTE) et l'autre en VARCHAR2(20 BYTE).
Quel doit être mon format optimal pour ces deux cas alors ?

Citation:

Ensuite et là ce n'est pas intuitif, la valeur du nls_numeric_caracters
est une chaîne dans une chaîne, il faut donc doubler les quotes :

Code:

'NLS_NUMERIC_CHARACTERS='' .'''

Alors ça je n'aurais jamais deviné après ce que j'ai lu dans la doc Oracle qui semble donc n'être pas du tout explicite sur ce point. 8O
Citation:
Enfin, il ne faut pas intervertir le caractère des milliers et de la décimale :
Code:

1 2 3 4 5 6 7 8 select to_number('11 800,73', '999G999G999D99', 'NLS_NUMERIC_CHARACTERS='' ,''') as col from dual; -- ORA-01722: invalid number select to_number('11 800,73', '999G999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') as col from dual; COL -------- 11800.73
Là j'ai du mal comprendre la doc. Cela voudrait dire qu'il faut donner les NLS_NUMERIC_CHARACTERS non pas dans l'ordre où ils sont dans la chaîne de caractères (logiquement, le séparateur de milliers d'abord puis le séparateur décimal) mais dans l'ordre inverse ? 8O

Je n'ai pas Oracle à la maison pour tester (et pas envie de me farcir son installation) donc ça attendra lundi.

Merci pour la réponse en tout cas.

07/12/2012, 18h43
Waldar

Citation:

Envoyé par CinePhil

Quel doit être mon format optimal pour ces deux cas alors ?

9G999G999G999G999D99 pour le 20 et beaucoup plus long pour le 50, à avoir si c'est utile quand même.

Citation:

Envoyé par CinePhil

Là j'ai du mal comprendre la doc. Cela voudrait dire qu'il faut donner les NLS_NUMERIC_CHARACTERS non pas dans l'ordre où ils sont dans la chaîne de caractères (logiquement, le séparateur de milliers d'abord puis le séparateur décimal) mais dans l'ordre inverse ? 8O

En effet il faut fouiller un peu dans la doc.

En recherchant dans TO_NUMBER :

Citation:

The 'nlsparam' argument in this function has the same purpose as it does in the TO_CHAR function for number conversions. Please refer to TO_CHAR (number) for more information.

En suivant le lien :

Citation:

This argument can have this form:

'NLS_NUMERIC_CHARACTERS = ''dg''

d pour decimal, g pour group.

Citation:

Envoyé par CinePhil

Je n'ai pas Oracle à la maison pour tester (et pas envie de me farcir son installation) donc ça attendra lundi..

Je ne travaille plus sur Oracle depuis un an (snif), mais je me suis créé un compte sur apex.oracle.com pour tester les petites requête, c'est pratique.
07/12/2012, 19h52
CinePhil

Citation:

En suivant le lien :

Citation:

This argument can have this form:

'NLS_NUMERIC_CHARACTERS = ''dg''

J'avais vu cet extrait mais ne l'avais pas interprété comme devant respecter l'ordre d puis g.

Pas claire la doc !

Après avoir passé du temps à corriger quelques données, nous revenons à cette procédure qui nous pose problème...

Au début de la procédure il y a maintenant ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 -- Liste des RAR à créer dans jefyco CURSOR cur_rar IS SELECT rar_date_cre, no_piece, substr(nom_du_tiers,1,50) as rar_nom_tiers, rar_cpt, rar_exo, rar_date_ema, rar_lib_objet, TO_NUMBER(rar_mht_in, '999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS rar_mht_in, TO_NUMBER(rar_mht_rest, '999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS rar_mht_rest, rar_relance, rar_no_jefyco FROM envt.rar_cocw WHERE import is null; c1 cur_rar%ROWTYPE;
En lançant la procédure, ou bien la requête SELECT toute seule, j'obtiens cette erreur :

Citation:

ORA-01722: Nombre non valide
01722. 00000 - "invalid number"

J'en conclus que mon TO_NUMBER n'est pas encore bon car quand je commente ces deux lignes, la requête s'exécute.

Faut-il vraiment mettre un format de 20 caractères pour un varchar2(20 byte) ?
C'est autre chose que j'ai mal interprété ?

Pour mémoire, les montants dans les deux colonnes sont formatés de la sorte : 999 999,99

Il faut vérifier les données les plus longues pour s'assurer que le format est suffisant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
select rar_mht_in
  FROM envt.rar_cocw
 where length(rar_mht_in) = (select max(length(rar_mht_in)) from envt.rar_cocw);
 
select min(rar_mht_in), max(rar_mht_in)
  FROM envt.rar_cocw;
 
select rar_mht_rest
  FROM envt.rar_cocw
 where length(rar_mht_rest) = (select max(length(rar_mht_rest)) from envt.rar_cocw);
 
select min(rar_mht_rest), max(rar_mht_rest)
  FROM envt.rar_cocw;

11/12/2012, 15h56
CinePhil
A priori oui puisque le montant maxi de la première colonne est 600 000,00.
Et dans la seconde les montants sont encore plus petits.

Je viens de tester avec le premier montant de la première colonne :
Code:

1 2 SELECT TO_NUMBER('6 000,00', '999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS nombre FROM DUAL
=> 6000
:ccool:
Code:

1 2 3 SELECT TO_NUMBER(rar_mht_in, '999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS nombre FROM rar_cocw WHERE no_piece = '546'
=> ORA-01722: Nombre non valide
:cry:

Chose intéressante...
Je copie le montant 6 000,00 dans SQL Developper et je le colle dans mon éditeur KWrite à la première requête :
Code:

1 2 SELECT TO_NUMBER('6*000,00', '999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS nombre FROM DUAL
=> ORA-01722: Nombre non valide
8O

En regardant bien dans mon éditeur, au lieu d'un espace, j'ai une sorte de caractère de soulignement en gris clair ! 8O

Y a t-il une fonction Oracle pour convertir une chaîne de caractères en code hexadécimal ou en code ASCII ?

EDIT :
Je pense que j'ai trouvé :

Code:

SELECT DUMP('6*000,00', 16) FROM DUAL

=> Typ=96 Len=8: 36,a0,30,30,30,2c,30,30
Pour un espace, il devrait y avoir 20. Pas banal ! 8O

EDIT 2 :
J'ai collé cet espace bizarre dans la requête et j'ai eu les bon nombres.

Ensuite, je l'ai collé dans les fonctions TO_NUMBER de ma procédure et elle a pu s'exécuter.
:ccool:

Tordu ces importation de données d'un autre logiciel ! :roll:
11/12/2012, 16h19
Waldar
Peut-être l'espace insécable (alt+0160) ?
L'astérisque dans le premier sujet m'y avait fait pensé mais j'ai oublié par la suite - avec Chrome et sur ce forum, je vois des * au lieu des espaces insécables :
1. Espace : " "
2. Espace insécable : "*"
11/12/2012, 16h31
CinePhil

Probablement l'espace insécable en effet. Je ne sais pas à quelle étape de l'importation c'est apparu.
En gros on part d'une exportation en format Excel à partir d'un ancien logiciel de comptabilité et comme à la DSI nous sommes 2 linuxiens, il a été ouvert et trituré en format ods avec Libre Office puis enregistré en csv pour importation dans Oracle.
Et les espaces insécables figurent bien dans le fichier csv en tout cas !

Voilà le genre de truc bien chronophage auquel peut être confronté un informaticien ! :roll: