Invalid number, NLS_NUMERIC_CHARACTERS et chargement multitables

**babylone7** · 26/01/2009, 16h13

Bonjour,

J'ai lu tous les topics concernant ce souci, malheureusement quelque chose m'échappe.

Je lis qu'il faut setter la variable OS NLS_LANG, mais je n'ai pas accès à la machine, juste au dossier de travail me permettant de lancer SqlLoader, et de toute façon je ne saurais même pas où chercher

Que dois-je demander au DBA ?

Concernant NLS_NUMERIC_CHARACTERS, je ne comprends pas comment l'utiliser, notamment au niveau de la définition du format.

Je vous montre mon cas :
Il s'agit de charger des noms, prénoms et age.
Quand la ligne commence par 1 je veux charger dans une table T_FILLES,
quand la ligne commence par 2 je veux charger dans une table T_GARCONS.

Fichier de données (les espaces et casses variables sont volontaires pour tester upper et trim) :

Code :

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

1
2
3
4
5
1;DUPONT;JEANNE       ;27
2;DUBOIS;PATRICK;30
1;marteau   ;FRANCOISE;26
1;DE MACHIN;  Michelle;28
2;DURAND;NICOLAS;29

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
14
15
16
17
18
19
20
21
22
23
LOAD DATA
INFILE '/workfolder/my_data.dat'
BADFILE '/workfolder/my_data.bad'
APPEND
PRESERVE BLANKS  
INTO TABLE T_FILLES   
when (1:1) = '1'
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( 
 	NOM_FILLE "upper(trim(:NOM_FILLE))",
	PRENOM_FILLE "upper(trim(:PRENOM_FILLE))",
	AGE_FILLE
)
INTO TABLE T_GARCONS  
when (1:1) = '2' 
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
 	NOM_GARCON "upper(trim(:NOM_GARCON))",
	PRENOM_GARCON "upper(trim(:PRENOM_GARCON))",
	AGE_GARCON
)

Résultat :

Rien dans la table T_FILLES, et les ages des garçons insérés dans le champ NOM_GARCON de la table T_GARCONS.

1ere question : comment charger ces données dans ces 2 tables ?

Ensuite, j'ai simplifié mon fichier de contrôle pour ne travailler que sur la table T_FILLES à laquelle j'ai ajouté une colonne NUM qui insère bien les valeurs "1" histoire de ne pas avoir de souci avec cette colonne et de tester l'insertion des ages... :

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
 
LOAD DATA
INFILE '/workfolder/my_data.dat'
BADFILE '/workfolder/my_data.bad'
APPEND
PRESERVE BLANKS  
INTO TABLE T_FILLES   
when (1:1) = '1'
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( 
 	NUM,
        NOM_FILLE "upper(trim(:NOM_FILLE))",
	PRENOM_FILLE "upper(trim(:PRENOM_FILLE))",
	AGE_FILLE "TO_NUMBER(:AGE_FILLE, '0d0','NLS_NUMERIC_CHARACTERS=''.,''')" 
)

C'est là que j'obtiens pour les 3 filles
Record 1: Rejected - Error on table CONFO.T_FILLES, column AGE_FILLE.
ORA-01722: invalid number
(idem Record 3 et Record 4)

En lisant les différents topics j'ai lancé les requêtes permettant de vérifier le NLS_NUMERIC_CHARACTERS :

Code :

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

SELECT to_number('1.2') FROM dual;

--> Résultat : 1.2

Code :

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

SELECT to_number('1,2') FROM dual;

--> Résultat : invalid number

Code :

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

SELECT to_number ('1.2', '9999999999999D9999999999', 'NLS_NUMERIC_CHARACTERS=''. ''') FROM dual;

--> Résultat : 1.2

J'en déduis que le NLS_NUMERIC_CHARACTERS est bien un point non ?..

2eme question : dois-je intervenir qqpart pour ce souci de nombre ? Pourquoi cela ne fonctionne pas ?

Je lis qu'il faut setter la variable OS NLS_LANG, mais je n'ai pas accès à la machine, juste au dossier de travail me permettant de lancer SqlLoader, et de toute façon je ne saurais même pas où chercher Que dois-je demander au DBA ?

Concernant NLS_NUMERIC_CHARACTERS, je ne comprends pas comment l'utiliser, notamment au niveau de la définition du format. Je vois bien que mettre '0d0' stockerai les age sous la forme 29.0, mais si je ne veux pas de décimale ?

D'avance merci à tous

**couak** · 26/01/2009, 20h13

Pour NLS_NUMERIC_CHARACTERS, le premier caractère représente le séparateur décimal, et le second caractère représente le séparateur des milliers
Pour ton problème d'âge, je ne comprends pas trop ton problème : si les âges sont entiers, pourquoi tu t'embêtes à faire du formattage ?
Dans ta table pour le champ AGE tu met un entier, et dans ton fichier de contrôle pas besoin de spécifier quoique ce soit dans le formattage, il va se débrouiller tout seul, comme tu le fais déjà avec ton champ "NUM"

**babylone7** · 27/01/2009, 09h15

Merci Couak !

Pour l'instant je travaille avec une table simple pour me familiariser avec Sql Loader mais après j'aurais des nombres à décimales à charger.
OK pour l'age, c'est en effet ce qui se passe déjà.

Je vais tester avec des décimales.

**babylone7** · 27/01/2009, 10h48

Ca plante avec des décimales

Fichier de données (au passage la conversion en majuscule de Michèle me donne MICH¿LE

)

Code :

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

1
2
3
4
5
6
 
1;DU-PONT;JEANNE       ;27; 04091981 ; 1.548771
2;DUBOIS;PATRICK;30;29051979; 2.65871
1;marteau.plouf   ;FRANCOISE;26;04111982; 0.998
1;D'MACHIN;  Michèle;28;16051980;4.25487
2;DURAND;NICOLAS;29;13011980;1.3654

Fichier de controle :

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
18
 
LOAD DATA
INFILE '/workfolder/my_data.dat'
BADFILE '/workfolder/my_data.bad'
APPEND
PRESERVE BLANKS  
INTO TABLE T_FILLES   
when (1:1) = '1'
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( 
 	NUM,
 	NOM_FILLE "upper(trim(:NOM_FILLE))", 	
	PRENOM_FILLE "upper(trim(:PRENOM_FILLE))",
	AGE_FILLE,
	DDN_FILLE "TO_DATE (:DDN_FILLE,'DDMMYYYY')",
	TAUX_CHANGE "TO_NUMBER(:TAUX_CHANGE, '0d000000','NLS_NUMERIC_CHARACTERS=''.,''')"
)

Erreur :

Record 1: Rejected - Error on table T_FILLES , column TAUX_CHANGE.
ORA-01722: invalid number

**babylone7** · 27/01/2009, 13h56

Personne ?..

**Mathias44** · 27/01/2009, 15h44

Bonjour,

Et là ?

Code :

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

TAUX_CHANGE "TO_NUMBER(TRIM(:TAUX_CHANGE), '0d000000','NLS_NUMERIC_CHARACTERS=''.,''')"

**babylone7** · 27/01/2009, 16h02

C'est pareil

**babylone7** · 28/01/2009, 15h37

Personne ?

pour le chargement multi table j'ai suivi la FAQ d'Oracle en utilisant

Code :

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

POSITION(1:1)

pour insérer dans la table T_GARCONS afin qu'il reboucle bien sur l'ensemble des résultats, et ça boucle bien, mais l'nsertion de se fait pas à cause d'un autre invalid number sur AGE_GARCON.

Ce qui est bizarre c'est que le AGE_FILLE fonctionne bien et que l'insertion de AGE_GARCON est faite comme cette de AGE_FILLE ...

**babylone7** · 28/01/2009, 16h07

Ah bah j'ai une piste mais alors c'est très bizarre !

Si dans le fichier de contrôle je mets

Code :

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

1
2
3
4
5
6
7
8
9
10
11
TRAILING NULLCOLS
( 
 	NUM,
 	--DATE_CREA TIMESTAMP,
 	NOM_FILLE 	"upper(trim(:NOM_FILLE))", 	
	PRENOM_FILLE "upper(trim(:PRENOM_FILLE))",
	AGE_FILLE,
	DDN_FILLE "TO_DATE (:DDN_FILLE,'DDMMYYYY')",
	TAUX_CHANGE FILLER --"TO_NUMBER(:TAUX_CHANGE, '0d000000','NLS_NUMERIC_CHARACTERS=''.,''')"
 
)

l'insertion fonctionne et l'age est bien inséré

si je mets

Code :

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

1
2
3
4
5
6
7
8
9
10
11
TRAILING NULLCOLS
( 
 	NUM POSITION(1:1),
 	--DATE_CREA TIMESTAMP,
 	NOM_FILLE 	"upper(trim(:NOM_FILLE))", 	
	PRENOM_FILLE "upper(trim(:PRENOM_FILLE))",
	AGE_FILLE,
	DDN_FILLE 	"TO_DATE (:DDN_FILLE,'DDMMYYYY')",
	TAUX_CHANGE FILLER --"TO_NUMBER(:TAUX_CHANGE, '0d000000','NLS_NUMERIC_CHARACTERS=''.,''')"
 
)

l'insertion ne fonctionne plus et une erreur invalid number est remontée :

Rejected - Error on table T_FILLES, column AGE_FILLE.
ORA-01722: invalid number

**babylone7** · 29/01/2009, 08h37

please

**Snipah** · 29/01/2009, 11h24

Salut,

Je suis pas spécialement à l'aise avec SQL Loader, si ce cas te bloque vraiment, peut être pourrais tu déjà tenter d'insérer simplement les données dans une table temporaire T_PERSONNE, et les retravailler ensuite?

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 T_PERSONNE
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
 GENRE          INTERGER EXTERNAL ,
 NOM		CHAR ,
 PRENOM		CHAR ,
 AGE 		INTEGER EXTERNAL ,
 DDN		DATE 'DDMMYYYY' ,
 TAUX_CHANGE	FLOAT EXTERNAL
)

**babylone7** · 29/01/2009, 11h35

Merci beaucoup Snipah .
Le truc c'est que les tables T_FILLES et T_GARCONS sont déjà des tables temporaires dont les données sont retravaillées ensuite via une grosse procédure stockée

Je ne me vois pas faire à nouveau une table de plus qu'il faudra encore traiter, je vais plus m'en sortir avec 3 fichiers de logs

C'est juste un souci de nombre je pense ..

**babylone7** · 29/01/2009, 11h52

Bon j'avance encore !!
J'ai fait encore plus simple ! Une table avec une seule colonne 'TAUX_CHANGE' et seulement les taux de change dans le fichier et là ça fonctionne !

Ce n'est donc pas un problème de nombre.
J'ai donc réessayé en ajoutant à la fin de chaque ligne du fichier de données un ; (chose que je n'avais pas faite)
Vous allez rire (ou pas), ça fonctionne (bon par contre les nombres sont arrondis, pourquoi ?
1.548771 --> 1.549
0.998 --> ok 0.998
4.25487 --> 4.255)

Du coup il insère bien chez les filles mais pas les garçons car j'obtiens l'erreur "invalid number" mais je sais que ce n'est pas le pb en lui-même car si j'enlève le POSITION (1:1) cela fonctionne mais je ne peux pas insérer dans 2 tables différentes si je ne repositionne pas le "curseur"...

**Snipah** · 29/01/2009, 14h29

Peux tu re-poster ton fichier de contrôle et le fichier de données que t'utilises pour tes tests? Ce sera plus lisible si quelqu'un veut essayer de t'aider

**babylone7** · 29/01/2009, 17h31

C'est vrai Snipah, pardon

voici :

Fichier de données :

Code :

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

1
2
3
4
5
1;DU-PONT;JEANNE       ;27; 04091981 ; 1.548771;
2;DUBOIS;PATRICK;30;29051979; 2.65871;
1;marteau.plouf   ;FRANCOISE;26;04111982; 0.998;
1;D'MACHIN;  Michèle;28;16051980;4.25487;
2;DURAND;NICOLAS;29;13011980;1.3654;

Structure de la table T_FILLES :

NOM_FILLE VARCHAR,
PRENOM_FILLE VARCHAR,
AGE_FILLE NUMBER,
DDN_FILLE DATE,
TAUX_CHANGE NUMBER

Structure de la table T_GARCONS :

NOM_GARCON VARCHAR,
PRENOM_GARCON VARCHAR,
AGE_GARCON

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
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
LOAD DATA
INFILE '/workfolder/my_data.dat'
BADFILE '/workfolder/my_data.bad'
APPEND
PRESERVE BLANKS  
INTO TABLE T_FILLES   
when (1:1) = '1'
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( 
 	NUM FILLER,
 	NOM_FILLE "upper(trim(:NOM_FILLE))", 	
	PRENOM_FILLE "upper(trim(:PRENOM_FILLE))",
	AGE_FILLE,
	DDN_FILLE "TO_DATE (:DDN_FILLE,'DDMMYYYY')",
	TAUX_CHANGE "TO_NUMBER(:TAUX_CHANGE,'0d000000','NLS_NUMERIC_CHARACTERS=''.,''')"
)
 
INTO TABLE T_GARCONS   
when (1:1) = '2'
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( 
 	NUM FILLER POSITION(1:1),
 	NOM_GARCON "upper(trim(:NOM_GARCON))", 	
	PRENOM_GARCON "upper(trim(:PRENOM_GARCON))",
	AGE_GARCON
)

Problème restant : l'âge du garçon fait remonter une erreur "invalid number". Cette erreur disparait si j'enleve le "POSITION(1:1)" mais du coup il ne reboucle pas sur toutes les lignes pour insérer dans T_GARCONS, or je dois insérer dans 2 tables différentes (au final ce sera même 3 tables)

Il y a aussi le souci de l'accent : la conversion en majuscule de Michèle me donne MICH¿LE , mais bon ça àa m'inquiète moins

Merci !!

**babylone7** · 30/01/2009, 15h16

Bon je continue de faire des tests en long en large et en travers.

Petite évolution : j'ai fait le load de la table T_GARCONS en 1er :

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
18
19
20
21
22
23
24
25
26
27
28
LOAD DATA
INFILE '/workfolder/my_data.dat'
BADFILE '/workfolder/my_data.bad'
APPEND
PRESERVE BLANKS  
INTO TABLE T_GARCONS   
when (1:1) = '2'
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( 
 	NUM FILLER,
 	NOM_GARCON "upper(trim(:NOM_GARCON))", 	
	PRENOM_GARCON "upper(trim(:PRENOM_GARCON))",
	AGE_GARCON
)
 
INTO TABLE T_FILLES   
when (1:1) = '1'
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( 
 	NUM FILLER,
 	NOM_FILLE "upper(trim(:NOM_FILLE))", 	
	PRENOM_FILLE "upper(trim(:PRENOM_FILLE))",
	AGE_FILLE,
	DDN_FILLE "TO_DATE (:DDN_FILLE,'DDMMYYYY')",
	TAUX_CHANGE "TO_NUMBER(:TAUX_CHANGE,'0d000000','NLS_NUMERIC_CHARACTERS=''.,''')"
)

Je n'ai pas précisé POSITION(1:1) sur la table des filles mais j'ai ordonné les données différemment dans le fichier de données poru voir comment s'enchainaient les 2 load :

Code :

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

1
2
3
4
5
1;DU-PONT;JEANNE       ;27; 04091981 ; 1.548771;
1;marteau.plouf   ;FRANCOISE;26;04111982; 0.998;
1;D'MACHIN;  Michèle;28;16051980;4.25487;
2;DUBOIS;PATRICK;30;29051979; 2.65871;
2;DURAND;NICOLAS;29;13011980;1.3654;

Voici ce que j'obtiens :

TABLE T_GARCONS :

TABLE T_FILLES :
(NB : date_crea est remplie par un trigger)

Visiblement :

- la DDN est sautée a cause de la colonne DATE_CREA (?) est-ce que mettre la colonne DATE_CREA en dernier solutionnerai le pb ?

- les résultats ne sont pas reparcourus du début, normal vu que je n'ai pas précisé NUM FILLER POSITION(1:1) , mais alors du coup soit je le précise et j'ai des problèmes de format et l'insertion ne de fait pas, soit je ne le précise pas et l'insertion se fait n'importe comment ....

SOS

**babylone7** · 30/01/2009, 15h22

NB : la date de naissance n'est pas sautée a cause de la colonne DATE_CREA, j'ai testé avec 2 tables de structures identiques (nom, prénom, age pour chaque table) et avec le même fichier de données, j'ai à nouveau le taux de change dans la colonne "NOM_FILLE"

**babylone7** · 30/01/2009, 17h21

Les insertions dans les 2 tables s'enchainent bien , il faut mettre POSITION(1) et non POSITION(1:1)

Maintenant j'ai un souci de date

Fichier de données :

Code :

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

1
2
3
4
5
6
 
1;DU-PONT;JEANNE       ;27;04091981; 1.548771;
2;DUBOIS;PATRICK;30;29051979; 2.65871;
1;marteau.plouf   ;FRANCOISE;26;04111982; 0.998;
1;D'MACHIN;  Michèle;28;16051980;4.25487;
2;DURAND;NICOLAS;29;13011980;1.3654;

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
14
15
16
17
18
19
20
21
22
23
INTO TABLE T_FILLES   
WHEN NUM = '1'
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
( 
 	NUM 					,
 	DATE_CREA 	FILLER TIMESTAMP,
 	NOM_FILLE 		"upper(trim(:NOM_FILLE))", 	
	PRENOM_FILLE 	"upper(trim(:PRENOM_FILLE))",
	AGE_FILLE			,
	DDN_FILLE 		DATE "DDMMYYYY", 
	TAUX_CHANGE 	"TO_NUMBER(trim(:TAUX_CHANGE), '0d000000','NLS_NUMERIC_CHARACTERS=''.,''')"
)
INTO TABLE T_GARCONS  
when NUM = '2' 
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
 	NUM 						POSITION(1),
 	NOM_GARCON 			"upper(trim(:NOM_GARCON))",
	PRENOM_GARCON 	"upper(trim(:PRENOM_GARCON))",
	AGE_GARCON
)

Insertion OK dans la table T_GARCONS mais pas pour la table T_FILLES :

Record 1: Rejected - Error on table CONFO.TEST_FAN_FILLES, column DDN_FILLE.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 3: Rejected - Error on table CONFO.TEST_FAN_FILLES, column DDN_FILLE.
ORA-01847: day of month must be between 1 and last day of month

Record 4: Rejected - Error on table CONFO.TEST_FAN_FILLES, column DDN_FILLE.
ORA-01858: a non-numeric character was found where a numeric was expected

Je ne comprends pas ce pb de date !

**babylone7** · 03/02/2009, 14h46

Résolu !

Il fallait préciser une valeur (quelle qu'elle soit) pour DATE_CREA dans le fichier de données.

Fichier de données corrigé :

Code :

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

1
2
3
4
5
1;;DU-PONT;JEANNE       ;27;04091981; 1.548771;
2;DUBOIS;PATRICK;30;29051979; 2.65871;
1;;marteau.plouf   ;FRANCOISE;26;04111982; 0.998;
1;;D'MACHIN;  Michèle;28;16051980;4.25487;
2;DURAND;NICOLAS;29;13011980;1.3654;

Et là ça marche bien !

Invalid number, NLS_NUMERIC_CHARACTERS et chargement multitables

SQL*Loader Oracle

Discussions similaires

Partager

Partager