hobbssh49
asked on
Oracle SQLLOADER:Error 566
Hello,
Have any one gotten this error loading data utilizing SQLLOADER on a UNIX machine: SQL*Loader-566: partial record found at end of datafile
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue. I have verified that the last record is not a partial records. Any help would be greatly appreciated.
Here is the example of the control file:
load data
infile '/edi/gssls/867in/867IN.da
discardmax 7000
append into table indir_sales_edi_raw
fields terminated by "whitespaces"
(
record_id sequence(MAX,1),
ec_filerow_id position(1:3),
cv_dist_cust_id position(4:18),
cv_indir_cust_id position(19:35),
name1 position(36:75),
address1 position(76:110),
address2 position(111:145),
city position(146:175),
state position(176:181),
postal position(182:193),
phone position(194:217),
contact_name position(218:267),
ship_date position(268:275),
invoice position(276:297),
qty_shipped position(298:308),
qty_returned position(313:323),
upc_id position(328:345),
invoice_amount position(346:360),
ec_ext_tp_id position(361:376),
cv_isa_cntrl position(377:385),
cv_gs_cntrl position(386:394),
cv_st_cntrl position(395:403),
process_date position(404:411),
process_state CONSTANT "A")
~
Have any one gotten this error loading data utilizing SQLLOADER on a UNIX machine: SQL*Loader-566: partial record found at end of datafile
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue. I have verified that the last record is not a partial records. Any help would be greatly appreciated.
Here is the example of the control file:
load data
infile '/edi/gssls/867in/867IN.da
discardmax 7000
append into table indir_sales_edi_raw
fields terminated by "whitespaces"
(
record_id sequence(MAX,1),
ec_filerow_id position(1:3),
cv_dist_cust_id position(4:18),
cv_indir_cust_id position(19:35),
name1 position(36:75),
address1 position(76:110),
address2 position(111:145),
city position(146:175),
state position(176:181),
postal position(182:193),
phone position(194:217),
contact_name position(218:267),
ship_date position(268:275),
invoice position(276:297),
qty_shipped position(298:308),
qty_returned position(313:323),
upc_id position(328:345),
invoice_amount position(346:360),
ec_ext_tp_id position(361:376),
cv_isa_cntrl position(377:385),
cv_gs_cntrl position(386:394),
cv_st_cntrl position(395:403),
process_date position(404:411),
process_state CONSTANT "A")
~
is there an end-of-line (cf/lf) after the last record?
ASKER
no there is no end-of-line at the last record.
can you get one on there and see if that fixes the problem?
ASKER
I did add an end-of-line to the file but that did not fix the problem
you put the record length at 427 on the input. Is the last record 427 long? did you pad it, In fact, the end of lines aren't necessary when the line length is specified.
ASKER
Yes, the last record is 427 characters in lenght. No I did not pad the file with exact spaces. That what I though would fix the problem when I added the record lenght to the control file, but the records are not loading from the file. Is it possible on the control file to force those records to commit without the last record? I am not getting a discard file either.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much it work when I remove the "fix 427" from the control file
I got hit with setting the recordsize to the size of the data and not including the cr/lf... at least I'm pretty sure I did, it was several lifetimes (years) ago. Glad it works now. that is the main thing :)