Hi,
I am trying to load data into table using sqlldr from a xml file which is on unix machine.
XML File--
<PARTICIPANT>
<BIOMETRICS>
<WGT>175</WGT>
<HT-FT>6</HT-FT>
<HT-IN>0</HT-IN>
<BMI>23.7</BMI>
<BPSYS>119</BPSYS>
<BPDIA>68</BPDIA>
<CHL>187</CHL>
<FASTING>N</FASTING>
</BIOMETRICS>
</PARTICIPANT>
<PARTICIPANT>
<BIOMETRICS>
<WGT>175</WGT>
<HT-FT>6</HT-FT>
<HT-IN>0</HT-IN>
<CHL>187</CHL>
<FASTING>N</FASTING>
</BIOMETRICS>
</PARTICIPANT>
craete table ---
create table participant (wgt number, ht_ft number, ht_in number, bmi number, bp_sys number, bpdia number, chl number, fasting varchar2(1));
control file---
load data
infile './data/biometric.xml'
into table participant
TRAILING NULLCOLS
(
dummy filler char(15) terminated by "<PARTICIPANT>" ,
wgt char(10) enclosed by "<WGT>" and "</WGT>" ,
ht_ft char(10) enclosed by "<HT-FT>" and "</HT-FT>" ,
ht_in char(10) enclosed by "<HT-IN>" and "</HT-IN>" ,
bmi char(10) enclosed by "<BMI>" and "</BMI>" ,
bp_sys char(10) enclosed by "<BPSYS>" and "</BPSYS>" ,
bpdia char(10) enclosed by "<BPDIA>" and "</BPDIA>",
chl char(10) enclosed by "<CHL>" and "</CHL>" ,
fasting char(10) enclosed by "<FASTING>" and "</FASTING>"
)
here i am getting these sql loader errors ----
Record 1: Discarded - all columns null.
Record 2: Rejected - Error on table PARTICIPANT, column DUMMY.
Field in data file exceeds maximum length
Record 3: Discarded - all columns null.
Record 4: Rejected - Error on table PARTICIPANT, column DUMMY.
Field in data file exceeds maximum length
Record 5: Rejected - Error on table PARTICIPANT, column DUMMY.
Field in data file exceeds maximum length
Record 6: Rejected - Error on table PARTICIPANT, column DUMMY.
Field in data file exceeds maximum length
Record 7: Rejected - Error on table PARTICIPANT, column DUMMY.
Field in data file exceeds maximum length
Record 8: Rejected - Error on table PARTICIPANT, column DUMMY.
Field in data file exceeds maximum length
Record 9: Discarded - all columns null.
Record 10: Rejected - Error on table PARTICIPANT, column DUMMY.
Field in data file exceeds maximum length
Record 11: Discarded - all columns null.
Record 12: Discarded - all columns null.
Record 13: Discarded - all columns null.
Record 14: Discarded - all columns null.
Record 15: Discarded - all columns null.
Record 16: Rejected - Error on table PARTICIPANT, column DUMMY.
Field in data file exceeds maximum length
Record 17: Rejected - Error on table PARTICIPANT, column DUMMY.
Field in data file exceeds maximum length
Record 18: Discarded - all columns null.
Record 19: Rejected - Error on table PARTICIPANT, column DUMMY.
Field in data file exceeds maximum length
Record 20: Discarded - all columns null.
Record 21: Discarded - all columns null.
Table PARTICIPANT:
0 Rows successfully loaded.
10 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
11 Rows not loaded because all fields were null.
But there is no null values in the XML and also i am confused with
>> the use of FILLER for dummy column !!
>> when i was trying to varchar2 datatype instead of char then it gives me error (which datatype are supported here ?)
i am using --
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production