no terminator found after TERMINATED and ENCLOSED field using Oracle SQL Loader

Question

I am getting a "no terminator found after TERMINATED and ENCLOSED field" error in the Oracle SQL Loader utility, but only on the last row and it looks like the quotes in the last row are correct and match all the other rows.

Here is the control file:

load data
infile '449_Funder.csv'  "str '\n'"
into table HMI_RAW_FUNDER_A
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(

    FUNDER_ID,
    PROJECT_ID,
    FUNDER_CD,
    GRANT_ID,
    START_DT,
    END_DT,
    DATE_CREATED_DT,
    DATE_UPDATED_DT,
    USER_ID,
    DATE_DELETED_DT,
    EXPORT_ID "REPLACE(:EXPORT_ID, CHR(13), '')",
    ROW_NBR SEQUENCE (2, 1)
    ,FILE_ID CONSTANT 1485

)

Parameter file:

CONTROL=449_Funder.ctl
ERRORS=9999
LOG=449_Funder.log
SKIP=1
ROWS=1000
SILENT=(HEADER, FEEDBACK)
DISCARD=449_Funder.dsc

Contents of the 449_Funder.csv file:

FunderID,ProjectID,Funder,GrantID,StartDate,EndDate,DateCreated,DateUpdated,UserID,DateDeleted,ExportID
"36","10010000",34,"",2015-03-27,,2012-09-07 00:00:00,2014-08-26 11:10:11,"James C",,"5377d99c772750474e37d5291d18f597"
"37","10011000",34,"",2015-03-27,,2012-09-26 00:00:00,2014-08-26 11:10:53,"James C",,"5377d99c772750474e37d5291d18f597"
"256","10022000",34,"",2014-10-01,,2015-02-06 13:11:14,2015-02-06 13:11:14,"James C",,"5377d99c772750474e37d5291d18f597"
"71","10005000",34,"",2015-03-27,,2012-04-27 00:00:00,2014-08-26 11:42:42,"James C",,"5377d99c772750474e37d5291d18f597"
"72","10006000",34,"",2015-03-27,,2012-05-09 00:00:00,2014-08-26 11:43:40,"James C",,"5377d99c772750474e37d5291d18f597"
"91","10003000",34,"",2015-03-27,,2012-05-31 00:00:00,2014-08-26 11:59:37,"James C",,"5377d99c772750474e37d5291d18f597"
"92","10004000",34,"",2015-03-27,,2012-05-02 00:00:00,2014-08-26 12:00:26,"James C",,"5377d99c772750474e37d5291d18f597"

Here is the log file output:

value used for ROWS parameter changed from 1000 to 89
Record 7: Rejected - Error on table HMI_RAW_FUNDER_A, column EXPORT_ID.
no terminator found after TERMINATED and ENCLOSED field

Table HMI_RAW_FUNDER_A:
  6 Rows successfully loaded.
  1 Row 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:                 255074 bytes(89 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:             7
Total logical records rejected:         1
Total logical records discarded:        0

score 1 · Answer 1 · answered Apr 08 '15 at 22:22

1

This can occur if the last record missed a line feed character.

answered Apr 08 '15 at 22:22

Alex

21,273
10
61
73

Thank you for responding. I am able to load other files that do not have an ending line feed character on the last row (that I can see anyway). Do you know if there is any thing else that could cause the last row to get this error? – Justin D. Apr 09 '15 at 15:46

no terminator found after TERMINATED and ENCLOSED field using Oracle SQL Loader

1 Answers1