SQL LOADER : value too large ...

**magic charly** · 02/05/2006, 09h45

Bonjour,

je souhaiterais effectuer le chargement d'une base de données à une autre en utilisant des fichiers plats. J'ai réussi à charger presk l'ensemble de mes tables avec SQLLDR mais une me résiste ...

mon fichier plat est de la forme :

Code :

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

1
2
3
4
5
6
 
"ID_MES","DATE_MES","VALEUR","TYPE_MES","FREQ_MES"
"51950","05/04/2006 00:15:00","1","M","Q"
"51951","05/04/2006 00:15:00","-1","M","Q"
"51952","05/04/2006 00:15:00","422","A","Q"
"51953","05/04/2006 00:15:00","27","A","Q"

dans la base de données cible :

Code :

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

1
2
3
4
5
6
7
8
 
create table MA_TABLE(
DATE_MES DATE not null,
VALEUR FLOAT,
TYPE_MES VARCHAR2(1) not null,
FREQ_MES VARCHAR2(1) not null,
ID_MES NUMBER not null
)

mon fichier de controle: control_MM.txt

Code :

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

1
2
3
4
5
6
 
LOAD DATA INFILE 'ma_table.csv' 
INSERT 
INTO TABLE MA_TABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID_MES,DATE_MES,VALEUR,TYPE_MES,FREQ_MES)

la commande sqllr lancée

Code :

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

1
2
3
 
sqlldr control=control_MM.txt log=log_MM.txt
log=log_MM.txt bad=bad_MM.txt discard=discard_MM.txt direct=y errors=0 skip=1 userid=mon_login/pwd

l'erreur que j'obtiens
Record 13: Rejected - Error on table MA_TABLE, column TYPE_MES.
ORA-01401: inserted value too large for column

Config: Oracle 9.2

je ne sais vraiment plus dans quelle direction chercher. Un coup de main serait vivement apprécié

merci d'avance à ceux qui se pencheront sur le probleme

Magic Charly

**pifor** · 02/05/2006, 10h31

Donnez nous tous les éléments pour analyser l'erreur:
- la ligne 13 du fichier ma_table.csv
- le contenu des fichiers suivants:
log_MM.txt bad_MM.txt discard_MM.txt

**magic charly** · 02/05/2006, 10h38

pas de problèmes

ligne 13
"53292","05/04/2006 00:15:00","1","M","Q"
ligne 14
"53293","05/04/2006 00:15:00","-1","M","Q"

dans le fichier discard il n'y a rien
dans le fichier bad file (1 seule ligne )
"53293","05/04/2006 00:15:00","-1","M","Q"

j'ai l'impression qu'il interprete mal le -1

j'ai essayé de forcer le type en FLOAT dans le fichier de controle pour le champ VALEUR. ça ne change rien

merci pour votre aide

**magic charly** · 02/05/2006, 10h53

j'ai modifié mon fichier de controle comme suit :

Code :

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

1
2
3
4
5
6
7
 
LOAD DATA INFILE 'MM.csv'  & CV
TRUNCATE 
INTO TABLE MA_TABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(ID_MES,DATE_MES date "dd/mm/yyyy hh24:mi:ss", VALEUR FLOAT,  TYPE_MES CHAR(1), FREQ_MES
CHAR(1))

jobtiens le log suivant :

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
29
30
31
32
33
Number to load: ALL
Number to skip: 1
Errors allowed: 0
Continuation:    none specified
Path used:      Direct
Table MESURE_MAIN, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
I_MES                             FIRST     *   ,  O(") CHARACTER
DATE_MES                             NEXT     *   ,  O(") DATE dd/mm/yyyy hh24:mi:ss
VALEUR                               NEXT     4           FLOAT
TYPE_MES                             NEXT     1   ,  O(") CHARACTER
FREQ_MES                             NEXT     1   ,  O(") CHARACTER
Record 108: Rejected - Error on table MA_TABLE, column TYPE_MES.
Field in data file exceeds maximum length
Record 13: Rejected - Error on table MA_TABLE, column TYPE_MES.
ORA-01400: cannot insert NULL into (TYPE_MES)
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table MESURE_MAIN:
  0 Rows successfully loaded.
  2 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576
Total logical records skipped:          1
Total logical records rejected:         2
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        0
Total stream buffers loaded by SQL*Loader load thread:        0

peut etre cela donnera t il des infos supplémentaires?

13 "53292","05/04/2006 00:15:00","1","M","Q"
14 "53293","05/04/2006 00:15:00","-1","M","Q"

103 "51489","05/04/2006 00:15:00","1","M","Q"
104 "51558","05/04/2006 00:15:00","1","M","Q"

merci

**casgr1** · 02/05/2006, 10h54

Encore une qualite d'Oracle : les codes d'erreurs retournés.

Essaie voir de taper ORA-01401 dans google, il y a de fortes chances que tu y trouves ta solution.

**magic charly** · 02/05/2006, 11h00

c'est ce que je fais mais je ne trouve pas de réponse satisfaisant à mon probleme

**aline** · 02/05/2006, 11h11

Bonjour,

Type_mes est un varchar2(1).
Il ne peux donc prendre -1.
Il faux que tu le change en varchar2(2).

**magic charly** · 02/05/2006, 11h19

merci pour cette info

cependant.. TYPE_MES doit recevoir des valeurs comme "M" ou "Q"

c'est le champ valeur qui est un FLOAT qui reçoit "1" "-1" "0,422"

pensez vous que le fait que je sépare mes champs (fields) par des virgules plutot que des points virgule pourrait créer une confusion?

le champ float peut il recevoir la valeur -1 (ça semblerait logique mais bon..) ?

merci de votre attention

**aline** · 02/05/2006, 11h59

peux tu nous envoyer ton fichier bad complet?

**magic charly** · 02/05/2006, 13h33

en remplaçant optionally enclosed by '"' par
enclosed by '"' j'ai réussi a supprimer les erreurs que j'avais lorsque le champ Valeur prenait une valeur négative par contre les décimaux sont mal interprétées comme en témoigne le fichier bad ci dessous :

j'ai limité le nombre d'erreurs a 50 (errors=50 dans la commande sqlldr)

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
51667","05/04/2006 00:15:00","0,5982","M","Q"
"51702","05/04/2006 00:15:00","0,7632","M","Q"
"51704","05/04/2006 00:15:00","0,9686","M","Q"
"51708","05/04/2006 00:15:00","0,689666666666667","M","Q"
"46490","05/04/2006 00:15:00","0,2215","M","Q"
"46491","05/04/2006 00:15:00","0,9316","M","Q"
"46492","05/04/2006 00:15:00","0,38625","M","Q"
"46499","05/04/2006 00:15:00","0,411","M","Q"
"46504","05/04/2006 00:15:00","0,21775","M","Q"
"46505","05/04/2006 00:15:00","0,932","M","Q"
"46506","05/04/2006 00:15:00","0,38","M","Q"
"46511","05/04/2006 00:15:00","0,22175","M","Q"
"46512","05/04/2006 00:15:00","0,9319","M","Q"
"46513","05/04/2006 00:15:00","0,38425","M","Q"
"46518","05/04/2006 00:15:00","0,3045","M","Q"
"46519","05/04/2006 00:15:00","0,8912","M","Q"
"46520","05/04/2006 00:15:00","0,484","M","Q"
"46525","05/04/2006 00:15:00","0,33825","M","Q"
"46526","05/04/2006 00:15:00","0,9122","M","Q"
"46527","05/04/2006 00:15:00","0,42825","M","Q"
"46532","05/04/2006 00:15:00","0,193","M","Q"
"46533","05/04/2006 00:15:00","0,9328","M","Q"
"46534","05/04/2006 00:15:00","0,388","M","Q"
"19013","05/04/2006 00:15:00","0,98025","M","Q"
"18618","05/04/2006 00:15:00","0,932","M","Q"
"18619","05/04/2006 00:15:00","0,39775","M","Q"
"16890","05/04/2006 00:15:00","1060,33333333333","A","Q"
"16891","05/04/2006 00:15:00","614,666666666667","A","Q"
"53305","05/04/2006 00:15:00","291,333333333333","A","Q"
"53307","05/04/2006 00:15:00","596,666666666667","A","Q"
"53308","05/04/2006 00:15:00","931,666666666667","A","Q"
"53310","05/04/2006 00:15:00","326,666666666667","A","Q"
"53314","05/04/2006 00:15:00","1754,33333333333","A","Q"
"53320","05/04/2006 00:15:00","1068,66666666667","A","Q"
"53321","05/04/2006 00:15:00","816,666666666667","A","Q"
"53322","05/04/2006 00:15:00","906,666666666667","A","Q"
"53323","05/04/2006 00:15:00","1706,33333333333","A","Q"
"53324","05/04/2006 00:15:00","328,666666666667","A","Q"
"53326","05/04/2006 00:15:00","577,333333333333","A","Q"
"53329","05/04/2006 00:15:00","233,666666666667","A","Q"
"53330","05/04/2006 00:15:00","279,666666666667","A","Q"
"53331","05/04/2006 00:15:00","519,333333333333","A","Q"
"53332","05/04/2006 00:15:00","579,666666666667","A","Q"
"53333","05/04/2006 00:15:00","1717,33333333333","A","Q"

comment faire pour accorder un plus grand nombre de bits à un float?

**McM** · 02/05/2006, 13h39

Pourquoi tu déclares pas un NUMBER à la place d'un FLOAT ?
Y'a peut-être une erreur sur la virgule de séparation des décimales

**magic charly** · 02/05/2006, 13h54

c'est ce que je pense (pour la virgule) ...
le type NUMBER n'est pa reconnu par SQL LOADER

je ne vois pas comment corriger uniquement les , qui ne sont pas au niveau du nombre float. ça me permettrait de séparer les champs par des ; par exemple tout en conservant la virgule qui est correctement interprétée par oracle.

<mon fichier fait 3000000 de lignes à la main ça serait pesant>

ex:

"55388";"06/04/2006 12:00:00";"1,566";"M";"Q"

**aline** · 02/05/2006, 14h34

Et bien on a la réponse!
Tu ne peux pas séparer tes champs par des virgules et t'en servir en même temps comme séparateur.

deux solutions:

1:
Tu changes ton fichier en mettant des ; comme tu le suggérais (ou un . à la place de la virgule décimale).

2:
Tu loade toutes tes données dans une table intermédiaire. Et tu prends deux colonnes pour ta colonne VALEUR.
ensuite tu pourras facilement tout reconstruire dans ta table

**magic charly** · 02/05/2006, 16h00

je relance ma requete pour fabriquer le fichier TSV adapté (ce qui risque de prendre un peu de temps)

et j'essaie ça

merci !

**magic charly** · 02/05/2006, 17h14

j'ai essayé

en ayant un fichier du type :

"55388";"06/04/2006 12:00:00";"1,566";"M";"Q"

et en adaptant mon fichier de controle

Code :

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

1
2
3
4
5
6
LOAD DATA INFILE 'MM.tsv' 
TRUNCATE
INTO TABLE MA_TABLE
FIELDS TERMINATED BY ';' ENCLOSED BY '"'
(ID_MES,DATE_MES date "dd/mm/yyyy hh24:mi:ss", VALEUR FLOAT,  TYPE_MES CHAR(1), FREQ_MES
CHAR(1))

a ma plus grande surprise ça ne change rien....

il refuse toujours les décimaux & les entiers relatifs

d'autre part les insertions réalisées ne correspondent pas à la réalité:

attendu :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
DATE_MES VALEUR TYPE_MES FREQ_MES ID_MES
05/04/2006 00:15:00 1 M Q 51950
05/04/2006 00:15:00 1 M Q 51951
05/04/2006 00:15:00 1 M Q 51952
05/04/2006 00:15:00 1 M Q 51953
05/04/2006 00:15:00 1 M Q 52516
05/04/2006 00:15:00 1 M Q 52517
05/04/2006 00:15:00 1 M Q 52518
05/04/2006 00:15:00 1 M Q 53288
05/04/2006 00:15:00 1 M Q 53289
05/04/2006 00:15:00 1 M Q 53290
05/04/2006 00:15:00 1 M Q 53291
05/04/2006 00:15:00 1 M Q 53292

obtenu :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
DATE_MES VALEUR TYPE_MES FREQ_MES ID_MES
05/04/2006 00:15:00 0,00247485237196088 M Q 51950
05/04/2006 00:15:00 0,00247485237196088 M Q 51951
05/04/2006 00:15:00 0,00247485237196088 M Q 51952
05/04/2006 00:15:00 0,00247485237196088 M Q 51953
05/04/2006 00:15:00 0,00247485237196088 M Q 52516
05/04/2006 00:15:00 0,00247485237196088 M Q 52517
05/04/2006 00:15:00 0,00247485237196088 M Q 52518
05/04/2006 00:15:00 0,00247485237196088 M Q 53288
05/04/2006 00:15:00 0,00247485237196088 M Q 53289
05/04/2006 00:15:00 0,00247485237196088 M Q 53290
05/04/2006 00:15:00 0,00247485237196088 M Q 53291
05/04/2006 00:15:00 0,00247485237196088 M Q 53292

l'erreur qui est donnée dans le log est :
Rejected - Error on table MA_TABLE, column TYPE_MES.
Initial enclosure character not found

**pifor** · 02/05/2006, 17h28

Essayez d'utiliser pour la colonne qui est FLOAT plutôt le type NUMBER(x,y) avec x = nombre maximal de chiffres avant la virgule et y = nombre maximal de chiffres après la virgule.

**magic charly** · 03/05/2006, 09h09

je ne comprends pas ce que tu m'as indiqué ne semble pas reconnu par sqlldr a moins que je ne l'ai pas utilisé correctement (voir ci dessous)

Code :

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

1
2
3
4
 
SQL*Loader-350: Syntax error at line 7.
Expecting "," or ")", found "NUMBER".
(IDARC,DATE_MES date "dd/mm/yyyy hh24:mi:ss", VALEUR NUMBER(10,10),  T

j'ai essayé de passer en FLOAT EXTERNAL Parce que INTEGER, FLOAT, ..etc semblent etre pr sqlldr des types binaires. Mais le mot clé external ne semble pas être compris par sqlldr alors que j'ai trouvé sur internet de la doc l'utilisant.

je n'ai plus de pistes...

merci à vous tous

**pifor** · 03/05/2006, 09h15

En fait je voulais dire changer le type de la colonne dans la table et non changer dans le fichier de contrôle. De plus, sauf pour les données qui ont un format spécial (comme les dates), je conseille plutôt de ne mettre aucune indication de type dans le fichier de contrôle: sqlldr doit pouvoir se débrouiller tout seul en consultant la définition de la table et en faisant les conversions nécessaires.

**magic charly** · 03/05/2006, 09h41

j'ai passé la colonne de la table en NUMBER(8,12)

et je n'ai pas précisé de type dans le fichier de controle de SQLLDR pour cette colonne.

voici le contenu de mon fichier bad (aucune ligne n'a été acceptée)

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
"51950";"05/04/2006 00:15:00";"1";"M";"Q"
"51951";"05/04/2006 00:15:00";"1";"M";"Q"
"51952";"05/04/2006 00:15:00";"1";"M";"Q"
"51953";"05/04/2006 00:15:00";"1";"M";"Q"
"52516";"05/04/2006 00:15:00";"1";"M";"Q"
"52517";"05/04/2006 00:15:00";"1";"M";"Q"
"52518";"05/04/2006 00:15:00";"1";"M";"Q"
"53288";"05/04/2006 00:15:00";"1";"M";"Q"
"53289";"05/04/2006 00:15:00";"1";"M";"Q"
"53290";"05/04/2006 00:15:00";"1";"M";"Q"
"53291";"05/04/2006 00:15:00";"1";"M";"Q"
"53292";"05/04/2006 00:15:00";"1";"M";"Q"
"53293";"05/04/2006 00:15:00";"-1";"M";"Q"
"53294";"05/04/2006 00:15:00";"-1";"M";"Q"
"53295";"05/04/2006 00:15:00";"-1";"M";"Q"
"53296";"05/04/2006 00:15:00";"-1";"M";"Q"
"53297";"05/04/2006 00:15:00";"-1";"M";"Q"
"53298";"05/04/2006 00:15:00";"-1";"M";"Q"
"53299";"05/04/2006 00:15:00";"-1";"M";"Q"
"53301";"05/04/2006 00:15:00";"-1";"M";"Q"
"53301";"05/04/2006 00:15:00";"-1";"M";"Q"
"53302";"05/04/2006 00:15:00";"-1";"M";"Q"
"53303";"05/04/2006 00:15:00";"-1";"M";"Q"

l'erreur diagnostiquée dans le log est :

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
 
Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID_MES                              FIRST     *   ;  O(") CHARACTER
DATE_MES                             NEXT     *   ;  O(") DATE dd/mm/yyyy hh24:mi:ss
VALEUR                               NEXT     *   ;  O(") CHARACTER
TYPE_MES                             NEXT     1   ;  O(") CHARACTER
FREQ_MES                             NEXT     1   ;  O(") CHARACTER
 
Record 1: Rejected - Error on table MA_TABLE column VALEUR.
ORA-01438: value larger than specified precision allows for this column
Record 2: Rejected - Error on table MA_TABLE, column VALEUR.
ORA-01438: value larger than specified precision allows for this column
 
Record 3: Rejected - Error on table MA_TABLE, column VALEUR.
ORA-01438: value larger than specified precision allows for this column
Record 4: Rejected - Error on table MA_TABLE, column VALEUR.
ORA-01438: value larger than specified precision allows for this column
Record 5: Rejected - Error on table MA_TABLE, column VALEUR.
ORA-01438: value larger than specified precision allows for this column
Record 6: Rejected - Error on table MA_TABLE, column VALEUR.
ORA-01438: value larger than specified precision allows for this column