SQL*Loader-566
We saw errors SQL*Loader-566 when we import data from an external text file by SQL*Loader.
SQL*Loader-501: Unable to read file (new-incoming/data.txt)
SQL*Loader-566: partial record found at end of datafile
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
There're several errors in the message, SQL*Loader-501, SQL*Loader-566 and SQL*Loader-2026. In which, we noticed that SQL*Loader-566 indicates that partial record are found at the end of file, but we found nothing wrong.
Let's see the content of control file of SQL*Loader.
LOAD DATA CHARACTERSET UTF8 INFILE 'new-incoming/data.txt' "FIX 128"
APPEND INTO TABLE ERPAPP.PAY_HETERO
...
OK, it indicates that records are limited to 128 characters in length for every line.
Solution
In fact, SQL*Loader-566 actually says that some partial data exceed the fixed length of records (lines) defined in the control file of SQL*Loader.
After doing some investigation, we found some records are 132 characters in length which exceeds the defined limitation.
Now we have 2 choices. One is to find records that exceed the limit, i.e. 128 characters in this case, and then correct the data to align with the limitation of length.
The other way is to remove the fixed length limitation if you're unable or unwilling to correct the text file.
LOAD DATA CHARACTERSET UTF8 INFILE 'new-incoming/data.txt' "FIX 128"
APPEND INTO TABLE ERPAPP.PAY_HETERO
...
That is, we removed "FIX 128" from the control file of SQL*Loader.
Either way, we might lost some data, although the number of records are still the same.