Discussion :

[CinePhil]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

ORA-00907: parenthèse de droite absente

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

EDIT
Si je fais cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT TO_NUMBER('6 000,00', '9G999D99')
FROM DUAL

J'obtiens : 6000

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

J'obtiens :

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

[Waldar]

Plusieurs remarques :

1. C'est un varchar2 de combien en source ?
   En spécifiant le format il vaut mieux aller à la précision maximale.
2. 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 :

   Sélectionner tout - Visualiser dans une fenêtre à part

   'NLS_NUMERIC_CHARACTERS='' .'''

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

   Code :

   Sélectionner tout - Visualiser dans une fenêtre à part

   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

[CinePhil]

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 ?

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

'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.

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

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.

[Waldar]

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.

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 ?

En effet il faut fouiller un peu dans la doc.

En recherchant dans TO_NUMBER :

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 :

This argument can have this form:

'NLS_NUMERIC_CHARACTERS = ''dg''

d pour decimal, g pour group.

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.

[CinePhil]

En suivant le lien :

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 !

[CinePhil]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

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