Discussion :

[sabdoul]

Bonsoir, j'ai une préoccupation depuis 2 jours et ça commence a bien faire donc j'ai besoin de votre aide.
j'ai des données de type chaine de caractère dans le fichier plat et je les les convertir en type number avant leur insertion dans ma BDD oracle à travers sql loader car mes champs dans BDD sont déclarés avec le type number mais j'ai cette erreur

ORA-00604: une erreur s'est produite au niveau SQL récursif 1
ORA-01722: Nombre non valide

voici mon fichier de contrôle

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
LOAD DATA
APPEND
INTO TABLE ma table
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(CETABL char(5) ,
COPRODUIT char(35) ,
DESIG char(128),
QUANTITE QUANTITE "TO_NUMBER(:QUANTITE,'999G999D99')"
MONTANT_HT "TO_NUMBER(:MONTANT_HT,'999G999G999')",
MONTANT_TTC "TO_NUMBER(:MONTANT_TTC,'999G999G999')"
DATE Date "DD/MM/YYYY"
)

j'ai essayé en ajoutant les NLS_NUMERIC_CHARACTERS mais rien a faire.

voici comment se présente mes données

Code :

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

1
2
3
4
5
212;1101411;MAXIMOUSSE 24 NEW BLOH;120,00; 50 160;59 189;01/01/2013
212;1101411;MAXIMOUSSE 24 NEW BLOH;1 080,00;368 010;368 010;01/01/2013
212;1102101;BF4 BLOH X 36 MX;-2 520,00;-745 200;-879 336;01/01/2013
212;1102101;BF4 BLOH X 36 MX;2 520,00;686 980;686 980;01/01/2013
212;1102103;BF 8 580 GR X 24 MX;720,00;363 090;363 090;01/01/2013

je précise qu'il y a un séparateur d'espace entre les milliers et c'est pour cela que j'utilise G pour l'indiquer.

qu'est ce qu'il faut adapter à ces lignes pour que ça marche car j'ai essayer plusieurs syntaxe mais rien n'y faire ça ne marche pas

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
QUANTITE  "TO_NUMBER(:QUANTITE,'999G999D99')"
MONTANT_HT "TO_NUMBER(:MONTANT_HT,'999G999G999')",
MONTANT_TTC "TO_NUMBER(:MONTANT_TTC,'999G999G999')"
comme j'ai eu a le dire, j'ai essayé toute les possibilités je pense avec NLS_NUMERIC_CHARACTERS
 aussi voici la structure de ma table
table ACHAT_PRODUIT(
CODE_ETABL VARCHAR2(5),
CODE_PRODUIT VARCHAR2(35),
DESIGNATION VARCHAR2(128),
QUANTITE NUMBER,
MONTANT_HT NUMBER,
MONTANT_TTC NUMBER,
DATE_GENERATION DATE)

j'espère que j'ai été plus précis et merci de vouloir m'aider

[apaul]

Salut,

Oui, tu avais bien vu avec le NLS_NUMERIC_CHARACTERS, car ta session ne doit pas être configurée avec les bonnes valeurs. Pour vérifier:

Code :

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

select * from v$nls_parameters ;

Pour ton cas, on peut le préciser dans la fonction TO_NUMBER comme ca:

Code :

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

1
2
3
4
5
select TO_NUMBER('94 567,00', '99G999G999D00', 'NLS_NUMERIC_CHARACTERS ='', ''') n FROM dual;
 
         N
----------
     94567

Attention,, le NLS_NUMERIC_CHARACTERS et sa valeur est un string, donc il faut échapper les quotes:

Code :

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

NLS_NUMERIC_CHARACTERS =', ' => 'NLS_NUMERIC_CHARACTERS ='', '''

[sabdoul]

Merci de répondre j'ai essayer ses différentes méthodes et je précise que mon NLS_NUMERIC_CHARACTERS est une une virgule(,). mais rien n'y fait ça ne fonctionne pas

[apaul]

NLS_NUMERIC_CHARACTERS est en fait 2 caractères:
- Le séparateur pour les décimales: ',' (ici virgule)
- Le séparateur pour les groupes de mille: ' ' (ici espace)

Dans le code que j'ai indiqué plus haut, l'espace suivant la virgule a donc son importance.

[sabdoul]

Salut j'ai pris en compte cela le l'espace entre les milliers mais toujours la même erreur de nombre invalid

[apaul]

Ah, ok, désolé.
Je n'ai jamais utilisé SQL*Loader, mais à tout hasard:

Code :

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

1
2
3
...
QUANTITE QUANTITE "TO_NUMBER(:QUANTITE,'999G999D99')"
...

Ici, essaye de dire que c'est un char avant de la convertir:

Code :

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

1
2
3
...
QUANTITE char(50) TO_NUMBER(:QUANTITE,'999G999D99', 'NLS_NUMERIC_CHARACTERS ='', '''),
...

(c'est peut-être une typo, dans ce cas je m'excuse à nouveau...)
Edit: (et j'avais oublié une virgule à la fin de la ligne, sûrement une typo de ton message original)

[sabdoul]

Salut voici le fichier de contrôle

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
LOAD DATA 
APPEND
INTO TABLE ACHAT_PRODUIT
FIELDS TERMINATED BY ';'  
TRAILING NULLCOLS
(CODE_ETABL char(5) ,
CODE_PRODUIT char(35) ,
DESIGNATION char(128),
QUANTITE  "TO_NUMBER(:QUANTITE, '999G999G999D99', 'NLS_NUMERIC_CHARACTERS ='', ''')",
MONTANT_HT  "TO_NUMBER(:MONTANT_HT, '999G999G999D99', 'NLS_NUMERIC_CHARACTERS ='', ''')",
MONTANT_TTC  "TO_NUMBER(:MONTANT_TTC, '999G999G999D99', 'NLS_NUMERIC_CHARACTERS ='', ''')",
DATE_GENERATION Date "DD/MM/YYYY"
)

mais toujours la même erreur et ce qui est paradoxal ce que l'insertion se fait manuellement lorsque je suis sur sql plus avec la même syntaxe adaptée pour une insertion
un exemple

Code :

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

insert into  achat_produit(MONTANT_HT) values(TO_NUMBER('52 120,00', '999G999G999D99', 'NLS_NUMERIC_CHARACTERS ='', '''));

il n'y a pas de problème.

[skuatamad]

Avec ce fichier de control ça fonctionne :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
LOAD DATA infile 'chemin_du_fichier\test.txt'
APPEND
INTO TABLE ACHAT_PRODUIT
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(CODE_ETABL,
CODE_PRODUIT,
DESIGNATION,
QUANTITE "TO_NUMBER(:QUANTITE,'999G999D99','NLS_NUMERIC_CHARACTERS ='', ''')",
MONTANT_HT "TO_NUMBER(:MONTANT_HT,'999G999G999','NLS_NUMERIC_CHARACTERS ='', ''')",
MONTANT_TTC "TO_NUMBER(:MONTANT_TTC,'999G999G999','NLS_NUMERIC_CHARACTERS ='', ''')",
DATE_GENERATION Date "DD/MM/YYYY"
)

[apaul]

Au moins, ca confirme que le problème est du côté du fichier de contrôle.
Et même avec le type:

... QUANTITE char(50) "TO_NUMBER(:QUANTITE ...

Là c'est ma dernière idée...

Edit: ah... grillé... bon ben ne prends pas en compte ce message

[sabdoul]

Salut, j'ai eu a faire ce que apaul a proposer c'est à dire char(50) mais ça marche pas toujours.quant à la proposition de skuatamad c'est la même erreur. il ne fonctionne pas lui aussi. que faire ?

[sabdoul]

je pense que le problème se trouve au niveau du séparateur de milliers qui est un espace car les lignes ne comportant pas des milliers sont insérés contrairement au ligne avec des milliers qui eux ont des espaces séparateurs.
j'ai besoin de votre aide

[Scriuiw]

Un peu système D mais ça devrait fonctionner :

Code :

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

1
2
3
...
QUANTITE char(50) TO_NUMBER(REPLACE(:QUANTITE,' ',''),'999999D99'),
...

[apaul]

Ah... Peut-être que l'espace de ton fichier source n'est pas un vrai (mais un "espace insécable" ou autre...)

[skuatamad]

j'ai besoin de votre aide

Peux tu fournir un exemple de lignes dans le fichier qui ne fonctionne pas.

[sabdoul]

voici un exemple de ligne
212;1101411;MAXIMOUSSE 24 NEW BLOH;120,00;50 160;59 189;01/01/2013

[sabdoul]

Bonjour, mon problème ne se trouvait pas dans le fichier de controle mais plutôt dans la source des données en effet les espaces entre le milliers sont visuellement des espaces mais en réalité c'est un autre caractère donc cela génère les erreurs de nombre invalide. c'est en essayant de prendre ses données comme des chaines de caractères en changeant le type des champs QUANTITE,MONTANT_HT... en varchar2 et après les avoir insérés dans la BDD que j'ai vu le caractère 'á' apparaitre a la place des espaces.
est-il possible avec sqlloader de remplacer avec REPLACE des caractères dont on connait pas la nature ou tout autre méthode pour régler ce cas.
j'ajoute que les données sont celle d'une BDD hyperfile de windev et sont formatés par celui-ci en chaine de caractère avant d’être envoyés. merci de votre aide

[al1_24]

Tu peux utiliser la fonction TRANSLATE à cet usage.

Code :

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

TRANSLATE(monNombre, ',.0123456789' || monNombre, '..0123456789')

Tous les caractères qui n'appartiennent pas à la chaine ', .0123456789' sont abandonnés et de surcroit la virgule est convertie en point.
N'est-ce pas ce que tu recherches ?

[sabdoul]

Merci j'essaie cela et je vous informe mais une préoccupation concernant la syntaxe comment s'y prendre avec Translate dans le fichier de controle.
exemple:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
LOAD DATA 
APPEND
INTO TABLE ACHAT_PRODUIT
FIELDS TERMINATED BY ';'  
TRAILING NULLCOLS
(CODE_ETABL char(5) ,
CODE_PRODUIT char(35) ,
DESIGNATION char(128),
QUANTITE  "TO_NUMBER(TRANSLATE(:QUANTITE, ',.0123456789' || QUANTITE, '..0123456789'),'999999.9999')",
MONTANT_HT  "TO_NUMBER(TRANSLATE(:MONTANT_HT, ',.0123456789' || MONTANT_HT, '..0123456789'),'99999999.9999')",
MONTANT_TTC  "TO_NUMBER(TRANSLATE(:MONTANT_TTC, ',.0123456789' || MONTANT_TTC, '..0123456789'),'999999.9999')",
DATE_GENERATION Date "DD/MM/YYYY"
)

Où dois-je enlever les ':' précédant le nom des champs dans la fonction TRANSLATE ou encore les rajouter en plus sur le nom du deuxième nom du champ c'est à dire QUANTITE "TO_NUMBER(TRANSLATE(:QUANTITE, ',.0123456789' || :QUANTITE, '..0123456789'),'999999.9999')", .
ou si on peut les encadrer par les les cotes ('QUANTITE').merci

[mnitu]

Utilisez le caractère á dans NLS_NUMERIC_CHARACTERS

Code :

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

1
2
 
Select to_number('50á160','999G999G999D99','NLS_NUMERIC_CHARACTERS ='',á''') from dual

[sabdoul]

Cette methode ne fonctionne pas car le caractère exact n'est pas surement 'á' car si c’était ça aurai marché. je suppose qu'oracle remplace ce caractère(espace du milieu) inconnu par le 'á'.
j'aimerais si possible avoir la syntaxe exact avec la fonction TRANSLATE dans le fichier de controle car cette solution proposé par Al1 me parait viable. merci