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 :
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.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.
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:
Fichier CTL:
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
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 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")
Merci d'avance pour votre aide. J'èspere que nous pourrons trouver une solution rapidement
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
Partager