SQL*Loader-510 Error - Comment le résoudre?
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 :
Citation:
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:
1
2
|
$SQLLDR silent=all control=${SCRIPTDIR}/myscript.ctl log=${LOGDIR}/myscript.log bad=${LOGDIR}/myscript.bad errors=200000 bindsize=750000 |
Fichier CTL:
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
|
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:
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 :(
