[SQL Loader] valeur trop large mais laquelle ?

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:

---

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:

---

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:

---

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

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 ?

Tu as un fichier de log pour ton SQL LOADER ?

Salut et merci elitost et Fred_D les lève tôt !
On dirait un chat tellement ca va vite :D
Ca fait du bien avec un chef qui attend depuis 2 jours :oops:

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:

---

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:

---

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 !

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

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

Code:

---

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 :roll: pfiou n'a pas idée de créer autant de champs !

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 :cry: et vous tiendrais au courrant ...

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

Citation:

---

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 :cry: 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 ;)

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 :?

+1, probablement que la virgule pose problème :)

Bonjour ,
Est ce que tu peux esayer de remplacer :

Citation:

---

FIELDS TERMINATED BY ";"

---

par

Citation:

---

FIELDS TERMINATED BY ';'

---

Dans le fichier de coontrôle

Jaouad

des bonnes idées pleuvent ce matin :o

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

Code:

---

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:

---

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:

---

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:

---

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

---

mais toujours le même message d'erreur aussi :cry:

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 :roll:

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

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

Merci

Jaouad

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

Code:

---

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

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

Code:

---

1 2	MOV2_AYEAR NUMBER(4)

---

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

Code:

---

1 2	MOV2_AYEAR,

---

j'obtienne du CHARACTER dans le fichier log:

Code:

---

1 2	MOV2_AYEAR NEXT * ; O(") CHARACTER

---

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

Code:

---

1 2	MOV2_AYEAR NUMERIC,

---

merci encore :D

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 :

Citation:

---

MOV2_CR
MOV2_CR_CUR
MOV2_CR_PIVOT

---

par :

Citation:

---

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 :wink:

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

Citation:

---

nls_numeric_characters=','

---

Jaouad

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:

Citation:

---

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 :?

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 !!! 8O :cry: :cry:
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:

---

1 2 3 4 5

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

---

Fichier texte:

Code:

---

DP;

---

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

Fichier log de résultat

Code:

---

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.

---

Le BADFile:

Code:

---

DP;਍

---

Ouarf alors là un caractère de longueur 2 qui ne rentre pas dans un VARCHAR2(4) ????
Au secours 8O please help me :wink:

Tiens, une table Arcole :D

As tu essayé de mettre dans le fichier control la description complète des champs ? Cela permettrais de fixer les numériques et les dates pour éviter que cela soit pris en compte comme des caractères... Bien que je doute que cela vienne de là.

Quant aux deux carrés, ca doit correspondre aux retour-chariots convertis lors d'une copie.

Sinon, il faudrait peut être passer en mode trace lors du sql loader pour voir exactement quel requête d'insertion il fait lors de son traitement, et ensuite désactiver la trace.

Exact CD ! Une table Arcole de "transition" pour interface comptable ;)

J'ai fait même pire que ta solution (regarde mon post précédent de plus prêt c'est la page2 je pense tu l'as zappée): j'ai tenté en injectant uniquement 1 seul champ à la fois ...

Alors voilà j'ai trouvé le champ qui coince: le tout premier: COMPANY.

Car en essayant uniquement avec le champ MOV2_NAME qui est un VARCHAR2(32) en lui injectant le texte:

Code:

---

FACTURE

---

celà m'a sorti un message d'erreur de contrôle de trigger sur l'insertion dans la table ... donc la longueur est bonne pour lui.

Mais en essayant avec 1 seul champ : COMPANY qui est un VARCHAR2(4) en lui injectant le texte

Code:

---

DP

---

là j'ai mon fameux message d'erreur sur la longueur trop grande :roll:

J'ai tenté en lui précisant

Code:

---

char(4)

---

dans mon fichier control , même chose...

pourquoi donc 7 caractères vont dans un VARCHAR2(32) et 2 caractères ne vont pas dans un VARCHAR2(4) ? 8O gloups

Merci (oui je sais c'est Dallas par ici :P )

Ca y est :D :P :o 8) :lol:

Comme promis mon propre dépannage pour la petite histoire:
j'ai refait mon fichier texte qui contenait les données avec un simple copier/coller dans un BlocNotes et voilà !!! 8O

Sans doute le format du fichier texte était-il mauvais (unicode au lieu d'ansi ?)...

En tout cas un énorme merci à tous ceux qui se sont penchés sur le problème, ils m'ont apportés plein d'astuces de vérifications et une solution en avance (j'ai le beug du format numérique maintenant ;).

Voili voilà ce fut un plaisir très instructif de poster chez developpez.com !
J'y ferai qqs tours pour voir si l'une ou l'autre question me parle (je suis assez noob en Oracle mais j'essaierai :oops: )

A bientôt ! :D