Bonjour,

Nous avons un program ORACLE qui fait l'export d'une base de données et l'envois ensuite par FTP sur un autre serveur.
Sur ce serveur nous avons un autre programme qui lui insère toutes les données de ce fichier ds une Table de la base de donnée Oracle(8.XX) locale.

Il ne m'est pas possible de rentrer dans les détails du programme Oracle faisant l'export.
Lorsque le programme "local" tente d'insérer les données de ce fichier dans la DB locale voici l'erreur que j'ai :

SQL*Loader-510: Physical record in data file( EXTRACTxxx) is longer than the maximum(1048576)
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
J'immagine que c'est du à une erreur dans le fichier vu que je parviens à lancer le script sans erreur en utilisant un autre fichier (version antérieure des données). Seulement voila, le fichier fait +-150mb il m'est impossible de le parcourir (déja très difficile de l'ouvrir) et vérifier chaque records jsuqu'a ce que je trouve une erreur.

Comment pourrais-je procéder afin de trouver ce qui pose problème?
Et en attendant il me faudrait au moin une méthode pour continuer l'insertion même si il y a un problème qqpart avec un record (un genre de skip).

Commander pour lancer le script:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
 
$SQLLDR silent=all control=${SCRIPTDIR}/myscript.ctl log=${LOGDIR}/myscript.log bad=${LOGDIR}/myscript.bad errors=200000 bindsize=750000
Fichier CTL:
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
 
LOAD DATA
  INFILE 'MYFILE.XX'
   INTO TABLE MYTABLE APPEND
    TRAILING NULLCOLS
    (SEQUENCE CHAR ENCLOSED BY "|" AND "|",
     RELATED_ITEM CHAR TERMINATED BY "|",
     RELATED_ITEM_EF CHAR TERMINATED BY "|",
     RELATED_ITEM_REV CHAR TERMINATED BY "|",
     BOM_LEVEL CHAR TERMINATED BY "|",
     ASSEMBLY_ID_TYPE CHAR TERMINATED BY "|",
     ASSEMBLY_ID CHAR TERMINATED BY "|",
     ASSEMBLY_ID_EF CHAR TERMINATED BY "|",
     ASSEMBLY_DESCRIPTION CHAR TERMINATED BY "|",
     BOM_DETAIL CHAR TERMINATED BY "|",
     ITEM_ID CHAR TERMINATED BY "|" ,
     ITEM_ID_EF CHAR TERMINATED BY "|" ,
     ITEM_DESCRIPTION CHAR TERMINATED BY "|" ,
     QUANTITY CHAR TERMINATED BY "|", 
     REF_DES CHAR(2000) TERMINATED BY "|",
     UOM CHAR TERMINATED BY "|" ,
     MAKE CHAR TERMINATED BY "|" ,
     START_DATE DATE "DDMMYYYY" TERMINATED BY "|" ,
     END_DATE DATE "DDMMYYYY" TERMINATED BY "|" ,
     ECO CHAR TERMINATED BY "|" ,
     COMMENTS CHAR TERMINATED BY "|" ,
     ITEM_TYPE CHAR TERMINATED BY "|" ,
     EXPORT_DATE DATE TERMINATED BY "|",	
     ITEM_REV CHAR TERMINATED BY WHITESPACE,
     LOAD_DATE SYSDATE,
     ORG_ID CONSTANT "IR")
SqlLoader 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
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
 
SQL*Loader: Release 9.2.0.6.0 - Production on Fri Feb 22 07:00:09 2008
 
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
 
Control File: /xxx
Data File: EXTRACTXXX
Bad File: xxx.bad
Discard File: none specified
 
(Allow all discards)
 
Number to load: ALL
Number to skip: 0
Errors allowed: 200000
Bind array: 64 rows, maximum of 750000 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK, ERRORS and DISCARDS
 
Table YYY, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
 
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SEQUENCE FIRST * | CHARACTER 
| 
RELATED_ITEM NEXT * | CHARACTER 
RELATED_ITEM_EF NEXT * | CHARACTER 
RELATED_ITEM_REV NEXT * | CHARACTER 
BOM_LEVEL NEXT * | CHARACTER 
ASSEMBLY_ID_TYPE NEXT * | CHARACTER 
ASSEMBLY_ID NEXT * | CHARACTER 
ASSEMBLY_ID_EF NEXT * | CHARACTER 
ASSEMBLY_DESCRIPTION NEXT * | CHARACTER 
BOM_DETAIL NEXT * | CHARACTER 
ITEM_ID NEXT * | CHARACTER 
ITEM_ID_EF NEXT * | CHARACTER 
ITEM_DESCRIPTION NEXT * | CHARACTER 
QUANTITY NEXT * | CHARACTER 
REF_DES NEXT 2000 | CHARACTER 
UOM NEXT * | CHARACTER 
MAKE NEXT * | CHARACTER 
START_DATE NEXT * | DATE DDMMYYYY 
END_DATE NEXT * | DATE DDMMYYYY 
ECO NEXT * | CHARACTER 
COMMENTS NEXT * | CHARACTER 
ITEM_TYPE NEXT * | CHARACTER 
EXPORT_DATE NEXT * | DATE DD-MON-RR 
ITEM_REV NEXT * WHT CHARACTER 
LOAD_DATE SYSDATE
ORG_ID CONSTANT
Value is 'IR'
 
SQL*Loader-510: Physical record in data file( EXTRACTxxx) is longer than the maximum(1048576)
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
 
Table YYY:
0 Rows successfully loaded.
0 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.
 
 
Space allocated for bind array: 508096 bytes(64 rows)
Read buffer bytes: 1048576
 
Total logical records skipped: 0
Total logical records read: 0
Total logical records rejected: 0
Total logical records discarded: 0
 
Run began on Fri Feb zzz
Run ended on Fri Feb zzz
 
Elapsed time was: 00:00:00.29
CPU time was: 00:00:00.12
Merci d'avance pour votre aide. J'èspere que nous pourrons trouver une solution rapidement