[SQL Loader] valeur trop large mais laquelle ?

**pitre** · 25/10/2005, 09h14

Bonjour,

tout d'abord je travail en version SQL*Loader: Release 8.1.7.0.0.

Un énorme merci pour ce forum et les membres qui y répondent pcq je ne dispose pas du support Oracle hors de mes moyens

Alors voici, j'ai le fameux message :

Code :

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

1
2
Record 1: Rejected - Error on table MOV2.
ORA-01401: inserted value too large for column

Je sais ce qu'il signifie: une valeur est trop grande pour la capacité de la colonne ... Mais j'ai 76 colonnes qui sont mises à jour

!!
Et je suis surpris qu'Oracle n'indique pas de quelle colonne il s'agit dans laquelle j'essaie d'insérer une valeur torp grande :o

N'existe-t-il pas un moyen de connaître de quelle colonne il s'agit ?

Pour info voici mon fichier control ( et loin en dessous le fichier texte

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
load data
 infile 'e:\Batchs\InterfaceDistribox\MOV2bis.txt'
 into table MOV2
 fields terminated by ";" optionally enclosed by '"'
 TRAILING NULLCOLS 
(COMPANY,
 JOURNAL,
 MOV2_DPT,
 MOV2_AYEAR,
 MOV2_ACCT_DAY,
 MOV2_PERIODG,
 MOV2_PERIODA,
 MOV2_SYSDATE "to_date(:MOV2_SYSDATE,'DD/MM/YYYY')",
 MOV2_DOC,
 MOV2_SEQ,
 MOV2_ACCT,
 MOV2_DB,
 MOV2_CR,
 MOV2_SENSE,
 MOV2_CUR,
 MOV2_CUR_EXCH,
 MOV2_DB_CUR,
 MOV2_CR_CUR,
 MOV2_NAME,
 MOV2_CTR,
 MOV2_DATE_MOV "to_date(:MOV2_DATE_MOV,'DD/MM/YYYY')",
 MOV2_INITIAL,
 MOV2_TRANSAC,
 MOV2_LEDGER,
 MOV2_INVOICE,
 MOV2_BAL_CODE,
 MOV2_BAL_DATE "to_date(:MOV2_BAL_DATE,'DD/MM/YYYY')",
 MOV2_AGENT,
 MOV2_INITIAL_DEST,
 MOV2_ORDER,
 MOV2_CHEQUE,
 MOV2_A1,
 MOV2_A2,
 MOV2_A3,
 MOV2_PROJ,
 MOV2_QTY,
 MOV2_UNIT,
 MOV2_OVERHEAD,
 MOV2_CAT,
 MOV2_MATR,
 MOV2_MODE,
 MOV2_CODE,
 MOV2_NUMBER,
 MOV2_DATE_ECH "to_date(:MOV2_DATE_ECH,'DD/MM/YYYY')" ,
 MOV2_DATE_INIT "to_date(:MOV2_DATE_INIT,'DD/MM/YYYY')",
 MOV2_PRCENT,
 MOV2_AMOUNT,
 MOV2_DATE_1 "to_date(:MOV2_DATE_1,'DD/MM/YYYY')",
 MOV2_DATE_2,
 MOV2_DAY_LATE,
 MOV2_TAX_CODE,
 MOV2_TAX,
 MOV2_TYPE,
 MOV2_TAX_BASIS,
 MOV2_EXTRAIT,
 MOV2_FLAG_A,
 MOV2_DATE_REP,
 MOV2_SOLD_CUR,
 MOV2_SOLD,
 MOV2_FACTORING,
 MOV2_BLOCAGE,
 MOV2_DATEBLOCAGE "to_date(:MOV2_DATEBLOCAGE,'DD/MM/YYYY')",
 MOV2_FLAG_OK,
 MOV2_DAS2_RUBRIQUE,
 MOV2_SEQUENCE_LIAISON,
 MOV2_SEQUENCE_CLNT,
 MOV2_LED_TYPE,
 MOV2_DAS2_NATURE,
 MOV2_IDENT_DOC,
 MOV2_SEQUENCE_ARES,
 MOV2_LIGNE,
 MOV2_CUR_DIV,
 MOV2_CUR_MULT,
 MOV2_DB_PIVOT,
 MOV2_CR_PIVOT,
 MOV2_EXCH_SAISIE,
 MOV2_CALE_PIVOT,
 MOV2_CALE_GESTION)

et voici une ligne à insérer:

Code :

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

1
2
 
DP;V70;DP;;;;;21/10/2005;;;7062;0;2978,32;C;EUR;1;0;2978,32;FACTURE;4111;21/10/2005;RPT;VTE;33255;4;;;;;;;CIAL;;;;;;;1;;TA;0;;21/10/2005;21/10/2005;;;;;;;;1;0;;;;;;;;;0;;;;;;V70/4;;2;1;1;0;2978,32;1;;

Merci d'avance à celui ou celle qui a un tuyau pour repérer la ou les colonne(s) cause(s) de mon erreur.

Très cordialement,
Pitre

**orafrance** · 25/10/2005, 09h18

Bonjour et merci pour les encouragements

Est-ce que tu connais au moins une ligne qui pose problème et quelle est la description de la table MOV2 ?

**elitost** · 25/10/2005, 09h19

Tu as un fichier de log pour ton SQL LOADER ?

**pitre** · 25/10/2005, 09h31

Salut et merci elitost et Fred_D les lève tôt !
On dirait un chat tellement ca va vite

Ca fait du bien avec un chef qui attend depuis 2 jours

Effectivement j'ai oublié de vous dire concernant le log et les erreurs:

en fait la ligne de texte présentée dans mon post d'origine est en erreur par exemple et voici, en résumé, le message du .log:

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
 
Table MOV2, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
 
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COMPANY                             FIRST     *   ;  O(") CHARACTER            
JOURNAL                              NEXT     *   ;  O(") CHARACTER            
MOV2_DPT                             NEXT     *   ;  O(") CHARACTER            
MOV2_AYEAR                           NEXT     *   ;  O(") CHARACTER            
MOV2_ACCT_DAY                        NEXT     *   ;  O(") CHARACTER            
MOV2_PERIODG                         NEXT     *   ;  O(") CHARACTER            
MOV2_PERIODA                         NEXT     *   ;  O(") CHARACTER            
MOV2_SYSDATE                         NEXT     *   ;  O(") CHARACTER            
    SQL string for column : "to_date(:MOV2_SYSDATE,'DD/MM/YYYY')"
 
.........
 
Record 1: Rejected - Error on table MOV2.
ORA-01401: inserted value too large for column
 
Record 2: Rejected - Error on table MOV2.
ORA-01401: inserted value too large for column
 
Record 3: Rejected - Error on table MOV2.
ORA-01401: inserted value too large for column
 
Table MOV2:
  0 Rows successfully loaded.
  3 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.

Et oui je connais la description de la table MOV2: mais j'ai lu qu'il n'est pas nécessaire de repréciser les formats des colonnes pour une insertion dans une table existante, me tromperais-je ?
Sinon j'avais un problème sur les dates mais en rajoutant:

Code :

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

1
2
 
 MOV2_DATE_INIT "to_date(:MOV2_DATE_INIT,'DD/MM/YYYY')",

sur une autre table plus simple celà a fonctionné.

Donc voilà le message d'erreur ne m'aide pas trop à déterminer d'où vient l'erreur exactement.

J'espère avoir répondu à vos attentes de précisions.

Merci bcp bcp !

**elitost** · 25/10/2005, 09h43

La définition de la table MOV2 ( le script de création par exemple ) pourrait être bien utile

**pitre** · 25/10/2005, 09h58

ok attention il est long ...:
je ne l'avais pas fourni pcq je n'osais pas étant donné le nombre de colonnes impressionnant !

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
 
SQL> desc mov2;
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- -------------
 COMPANY                                            VARCHAR2(4)
 JOURNAL                                            VARCHAR2(3)
 MOV2_DPT                                           VARCHAR2(4)
 MOV2_AYEAR                                         NUMBER(4)
 MOV2_ACCT_DAY                                      NUMBER(2)
 MOV2_PERIODG                                       NUMBER(2)
 MOV2_PERIODA                                       NUMBER(2)
 MOV2_SYSDATE                                       DATE
 MOV2_DOC                                           NUMBER(8)
 MOV2_SEQ                                           NUMBER(6)
 MOV2_ACCT                                          VARCHAR2(8)
 MOV2_DB                                            NUMBER(14,2)
 MOV2_CR                                            NUMBER(14,2)
 MOV2_SENSE                                         VARCHAR2(1)
 MOV2_CUR                                           VARCHAR2(3)
 MOV2_CUR_EXCH                                      NUMBER(15,8)
 MOV2_DB_CUR                                        NUMBER(14,2)
 MOV2_CR_CUR                                        NUMBER(14,2)
 MOV2_NAME                                 NOT NULL VARCHAR2(32)
 MOV2_CTR                                           VARCHAR2(8)
 MOV2_DATE_MOV                                      DATE
 MOV2_INITIAL                                       VARCHAR2(4)
 MOV2_TRANSAC                                       VARCHAR2(4)
 MOV2_LEDGER                                        VARCHAR2(8)
 MOV2_INVOICE                                       VARCHAR2(17)
 MOV2_BAL_CODE                                      NUMBER(3)
 MOV2_BAL_DATE                                      DATE
 MOV2_AGENT                                         VARCHAR2(8)
 MOV2_INITIAL_DEST                                  VARCHAR2(4)
 MOV2_ORDER                                         VARCHAR2(17)
 MOV2_CHEQUE                                        VARCHAR2(10)
 MOV2_A1                                            VARCHAR2(12)
 MOV2_A2                                            VARCHAR2(12)
 MOV2_A3                                            VARCHAR2(12)
 MOV2_PROJ                                          VARCHAR2(12)
 MOV2_QTY                                           NUMBER(14,3)
 MOV2_UNIT                                          VARCHAR2(4)
 MOV2_OVERHEAD                                      VARCHAR2(12)
 MOV2_CAT                                           VARCHAR2(4)
 MOV2_MATR                                          VARCHAR2(8)
 MOV2_MODE                                          VARCHAR2(2)
 MOV2_CODE                                          VARCHAR2(2)
 MOV2_NUMBER                                        NUMBER(3)
 MOV2_DATE_ECH                                      DATE
 MOV2_DATE_INIT                                     DATE
 MOV2_PRCENT                                        NUMBER(5,2)
 MOV2_AMOUNT                                        NUMBER(14,2)
 MOV2_DATE_1                                        DATE
 MOV2_DATE_2                                        VARCHAR2(8)
 MOV2_DAY_LATE                                      VARCHAR2(4)
 MOV2_TAX_CODE                                      VARCHAR2(5)
 MOV2_TAX                                           NUMBER(1)
 MOV2_TYPE                                          NUMBER(1)
 MOV2_TAX_BASIS                                     NUMBER(14,2)
 MOV2_EXTRAIT                                       VARCHAR2(8)
 MOV2_FLAG_A                                        NUMBER(1)
 MOV2_DATE_REP                                      NUMBER(8)
 MOV2_SOLD_CUR                                      NUMBER(14,2)
 MOV2_SOLD                                          NUMBER(14,2)
 MOV2_FACTORING                                     VARCHAR2(8)
 MOV2_BLOCAGE                                       VARCHAR2(2)
 MOV2_DATEBLOCAGE                                   DATE
 MOV2_FLAG_OK                                       NUMBER(1)
 MOV2_DAS2_RUBRIQUE                                 VARCHAR2(4)
 MOV2_SEQUENCE_LIAISON                              NUMBER(12)
 MOV2_SEQUENCE_CLNT                                 NUMBER(12)
 MOV2_LED_TYPE                                      VARCHAR2(4)
 MOV2_DAS2_NATURE                                   NUMBER
 MOV2_IDENT_DOC                                     VARCHAR2(17)
 MOV2_SEQUENCE_ARES                                 NUMBER
 MOV2_LIGNE                                         NUMBER
 MOV2_CUR_DIV                                       NUMBER
 MOV2_CUR_MULT                                      NUMBER
 MOV2_DB_PIVOT                                      NUMBER(14,2)
 MOV2_CR_PIVOT                                      NUMBER(14,2)
 MOV2_EXCH_SAISIE                                   NUMBER(15,8)
 MOV2_CALE_PIVOT                                    NUMBER(14,2)
 MOV2_CALE_GESTION                                  NUMBER(14,2)

a y est

pfiou n'a pas idée de créer autant de champs !

**pitre** · 25/10/2005, 10h14

En toute honnêteté je ne veux pas vous demander de vérifier tous mes champs, c'est justement pcq cette recherche est fastidieuse (je l'ai effectuée une fois sans relever d'anomalie ...) que je cherchais une méthode plus rapide qui m'indique quel champ dépasse la largeur de sa colonne.
Maintenant si vous me dites qu'il n'y a pas le choix, je vais passer la prochaine demi-heure à tout revérifier encore et encore

et vous tiendrais au courrant ...

**orafrance** · 25/10/2005, 10h18

Bizarre, l'insert passe parfaitement... t'es sûr que la ligne que tu as donné pose problème ?

**orafrance** · 25/10/2005, 10h23

Envoyé par pitre

En toute honnêteté je ne veux pas vous demander de vérifier tous mes champs, c'est justement pcq cette recherche est fastidieuse (je l'ai effectuée une fois sans relever d'anomalie ...) que je cherchais une méthode plus rapide qui m'indique quel champ dépasse la largeur de sa colonne.
Maintenant si vous me dites qu'il n'y a pas le choix, je vais passer la prochaine demi-heure à tout revérifier encore et encore

et vous tiendrais au courrant ...

Copier, coller et remplacer permet de vérifier rapidement

remplace , par . et ; par ';' et enfin '' par NULL... déjà ça te fait quasiment la ligne d'insert

**plaineR** · 25/10/2005, 10h26

Je ne vois qu'un truc qui pourrait faire planter l'insertion, c'est tes nombres dont le séparateur de décimale dans le fichier est la virgule, ce qui n'est peut-être pas le cas dans ta base.

Mais cela ne semble pas avoir grand rapport avec le code erreur

**orafrance** · 25/10/2005, 10h34

+1, probablement que la virgule pose problème

**Jaouad** · 25/10/2005, 10h35

Bonjour ,
Est ce que tu peux esayer de remplacer :

FIELDS TERMINATED BY ";"

par

FIELDS TERMINATED BY ';'

Dans le fichier de coontrôle

Jaouad

**pitre** · 25/10/2005, 10h54

des bonnes idées pleuvent ce matin :o

J'ai testé le remplacement de la "," par un "." ce qui donne la line:

Code :

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

1
2
 
DP;V70;DP;;;;;21/10/2005;;;7062;0;2978.32;C;EUR;1;0;2978.32;FACTURE;4111;21/10/2005;RPT;VTE;33255;4;;;;;;;CIAL;;;;;;;1;;TA;0;;21/10/2005;21/10/2005;;;;;;;;1;0;;;;;;;;;0;;;;;;V70/4;;2;1;1;0;2978.32;1;;

Mais toujours le même message d'erreur:

Code :

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

1
2
3
 
Record 1: Rejected - Error on table MOV2.
ORA-01401: inserted value too large for column

J'avais déjà pensé à la virgule mais après avoir exécuté cette requête:

Code :

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

1
2
3
4
5
6
 
SQL> select min(mov_cr) from mov where company = 'DP' and journal='V70';
 
MIN(MOV_CR)
-----------
       2,62

Je me suis dit que mon Oracle était bien en décimal à virgules et non pas en point ... ou est-ce juste mon SQL*Plus qui m'affiche une , ?

Jaouad j'ai aussi testé ton idée

Code :

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

  fields terminated by ';' optionally enclosed by '"'

mais toujours le même message d'erreur aussi

Et Fred_D super belle ingéniosité pour l'idée du copier/coller et les remplacer judicieux ^^ j'ai aussi réussi à insérer la ligne grâce à ça ... y'a de quoi s'arracher les cheuveux

Gniarf c'est coriace va falloir passer en salle d'op

**Jaouad** · 25/10/2005, 10h57

peux tu mettre errors à 0 et nous donner la premiére ligne de ton Fichier BAD

Merci

Jaouad

**pitre** · 25/10/2005, 11h43

ok voilà (désolé pour le temps mis mais la compta m'a bloqué ils utilisent aussi la table ...):

Code :

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

1
2
3
 
DP;V70;DP;;;;;21/10/2005;;;7062;0;2978.32;C;EUR;1;0;2978.32;FACTURE;4111;21/10/2005;RPT;VTE;33255;4;;;;;;;CIAL;;;;;;;1;;TA;0;;21/10/2005;21/10/2005;;;;;;;;1;0;;;;;;;;;0;;;;;;V70/4;;2;1;1;0;2978.32;1;;
਍

Je ne sais pas si on voit bien mais j'ai 2 carrés en fin de ligne ...

**pitre** · 25/10/2005, 12h09

J'y pense: celà ne gène-t-il pas que j'ai dans la table MOV2:

Code :

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

1
2
 
MOV2_AYEAR                                         NUMBER(4)

et qu'en ne précisant pas le format dans le fichier control:

Code :

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

1
2
 
MOV2_AYEAR,

j'obtienne du CHARACTER dans le fichier log:

Code :

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

1
2
 
MOV2_AYEAR                           NEXT     *   ;  O(") CHARACTER

devrais-je préciser le format numérique ainsi dans mon fichier control ? :

Code :

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

1
2
 
MOV2_AYEAR NUMERIC,

merci encore

**Jaouad** · 25/10/2005, 12h11

ALors ton erreur est du simplement au séparateur décimal

si tu remplace le point par la vrigule ca passe ( en tout cas chez moi )

remplace dans le fichier de contrôle :

MOV2_CR
MOV2_CR_CUR
MOV2_CR_PIVOT

par :

MOV2_CR "replace(:mov2_cr,'.',',')" ,
MOV2_CR_CUR "replace(:mov2_cr_cur,'.',',')"
MOV2_CR_PIVOT "replace(:mov2_cr_pivot,'.',',')"

je vais essayer de te retrouver le paramétre qui va bien

**Jaouad** · 25/10/2005, 12h27

Sinon il existe une solution plus radical qui consiste à mettre dans le fichier d'init.ora

nls_numeric_characters=','

Jaouad

**pitre** · 25/10/2005, 13h23

désolé de pas tester ta solution de suite (je suis à la maison là comme quoi ca me "travail"

mais c'est intéressant comme solution

Mais en fait j'avais déjà testé le remplacement en direct dans mon fichier texte à importer des "," par des "." : je cite un de mes précédents posts:

J'ai testé le remplacement de la "," par un "." ce qui donne la line:
Code:

DP;V70;DP;;;;;21/10/2005;;;7062;0;2978.32;C;EUR;1;0;2978.32;FACTURE;4111;21/10/2005;RPT;VTE;33255;4;;;;;;;CIAL;;;;;;;1;;TA;0;;21/10/2005;21/10/2005;;;;;;;;1;0;;;;;;;;;0;;;;;;V70/4;;2;1;1;0;2978.32;1;;

Mais toujours le même message d'erreur:

donc voilà ... de retour au taf je testerai l'intégration d'une partie des champs puis d'une autre jusqu'à plantage pour tenter de détecter quel champ c'est.
Je vous tiens au courrant, pour la petite histoire, si je trouve

**pitre** · 25/10/2005, 14h41

Alors là j'y crois pas mes yeux: j'ai réduit le fichier à importer à 1 ligne, 1 champ et toujours le même message !!!

Même si je sais que le champ MOV2_NAME est obligatoire j'ai tenté tout de même pour voir ...

Voici les petits extraits:
Contrôle file:

Code :

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

1
2
3
4
5
load data
 infile 'e:\Batchs\InterfaceDistribox\MOV2tris.txt'
 into table MOV2
 fields terminated by ';'
(COMPANY)

Fichier texte:

Code :

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

DP;

j'ai aussi testé sans le ";" de la fin même résultat

Fichier log de résultat

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
SQL*Loader: Release 8.1.7.0.0 - Production on Tue Oct 25 14:33:40 2005
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
Control File:   E:\Batchs\InterfaceDistribox\loader_test.ctl
Data File:      e:\Batchs\InterfaceDistribox\MOV2tris.txt
  Bad File:     E:\Batchs\InterfaceDistribox\MOV2tris.bad
  Discard File:  none specified
 
 (Allow all discards)
 
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional
 
Table MOV2, loaded from every logical record.
Insert option in effect for this table: INSERT
 
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COMPANY                             FIRST     *   ;       CHARACTER            
 
Record 1: Rejected - Error on table MOV2.
ORA-01401: inserted value too large for column
 
 
Table MOV2:
  0 Rows successfully loaded.
  1 Row 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.