Discussion :

[DBA_doom]

Mon Probleme est le suivamt :
je veux charger un fichier texte ds une base de donnee oracle 9i mais j ai tjrs la meme erreur Commit point reached. aucune ligne n est charge. voila la table que je ve remplir :
create table TRC_ADM (
ID int not null ,
TIME date not null ,
RES int not null ,
RPL int not null ,
NE char(16) not null
)

Mon fichier control :
LOAD DATA
INFILE './TRC_DATA_nn.txt'
APPEND
INTO TABLE TRC_ADM
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID INTEGER EXTERNAL,
TIME CHAR,
RES INTEGER EXTERNAL,
RPL INTEGER EXTERNAL,
NE CHAR
)

mon fichier bat :
sqlldr SYSTEM/system@grantest errors=3000 control=test log=logdata data=TRC_DATA_nn.txt
pause

un extrait de mon fichier de donnee :
68442441;Feb 1 2007 12:00AM;1;60;BSCAGA2
68442442;Feb 1 2007 1:00AM;1;60;BSCAGA2
68442443;Feb 1 2007 2:00AM;1;60;BSCAGA2
68442444;Feb 1 2007 3:00AM;1;60;BSCAGA2
68442445;Feb 1 2007 4:00AM;1;60;BSCAGA2
68442446;Feb 1 2007 5:00AM;1;60;BSCAGA2
68442447;Feb 1 2007 6:00AM;1;60;BSCAGA2
68442448;Feb 1 2007 7:00AM;1;60;BSCAGA2
68442449;Feb 1 2007 8:00AM;1;60;BSCAGA2
68442450;Feb 1 2007 9:00AM;1;60;BSCAGA2

Mon log file :
Control File: test.ctl
Data File: TRC_DATA_nn.txt
Bad File: TRC_DATA_nn.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 3000
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table TRC_ADM, 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
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * ; O(") CHARACTER
TIME NEXT * ; O(") CHARACTER
RES NEXT * ; O(") CHARACTER
RPL NEXT * ; O(") CHARACTER
NE NEXT * ; O(") CHARACTER

Record 1: Rejected - Error on table TRC_ADM, column TIME.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 2: Rejected - Error on table TRC_ADM, column TIME.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 3: Rejected - Error on table TRC_ADM, column TIME.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 4: Rejected - Error on table TRC_ADM, column TIME.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 5: Rejected - Error on table TRC_ADM, column TIME.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 6: Rejected - Error on table TRC_ADM, column TIME.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 7: Rejected - Error on table TRC_ADM, column TIME.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 8: Rejected - Error on table TRC_ADM, column TIME.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 9: Rejected - Error on table TRC_ADM, column TIME.
ORA-01858: a non-numeric character was found where a numeric was expected

Record 10: Rejected - Error on table TRC_ADM, column TIME.
ORA-01858: a non-numeric character was found where a numeric was expected


Table TRC_ADM:
0 Rows successfully loaded.
10 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: 82560 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 10
Total logical records rejected: 10
Total logical records discarded: 0

j attend vos reponses avec impatience.

[pifor]

Probablement un problème de format pour la colonne de type DATE.

[DBA_doom]

oui c le champ DATE, aucune solution a proposer ???

[pifor]

Avez-vous essayé:

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
LOAD DATA 
INFILE './TRC_DATA_nn.txt' 
APPEND
INTO TABLE TRC_ADM 
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
(
ID INTEGER EXTERNAL,
TIME DATE LOAD DATA 
INFILE './TRC_DATA_nn.txt' 
APPEND
INTO TABLE TRC_ADM 
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
(
ID INTEGER EXTERNAL,
TIME DATE "MON DD YYYY HH:MMPM"
RES INTEGER EXTERNAL,
RPL INTEGER EXTERNAL,
NE CHAR
)

Voir les formats de date.

[sefaxe82]

essaye cette commande pour résoudre le pbm de la date :

Code :

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

TO_DATE('Feb 1 2007 12:00AM', 'MON DD YYYY HH24')

[DBA_doom]

un tres grand merci a pifor , vs etes un genie !!
j ai fait une ptite modifcation a ta solution et ca marche :
TIME DATE "MON DD YYYY HH:MIPM"