I am trying to load data from a file containing data that contains new line characters that need to be inserted together into a column. I am getting one multi-line record to insert, but another one won't - getting an error:
Record 12: Rejected - Error on table CUST, column NOTES.
Field in data file exceeds maximum length
The notes column is varchar2(4000), and this data is not more than 4000, so I'm not sure what's going on.
field delimiter is , record delimiter is |
CTL File (run from a .bat that names the actual data file - the infile is just a placeholder)
load data
infile ctest1.csv "str '|'"
into table CUST
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(CUST_NO,
NAME,
NOTES
)
Data:
"00000002","ACCREDITED AUTOBALE COR","",|
"00000003","MAINLINE ROAD & BRIDGE CONST","",|
"00000005","SMITH & CASHION PLC","",|
"00000006","BB & T/THORNTON & HARWELL INS","",|
"00000009","A C MILLER CONCRETE PRODUCTS","",|
"00000010","ABC SUPPLY","",|
"00000011","GATES & SONS INC","",|
"00000012","HEATEC INC","",|
"00000013","AJAX TOOLS EQUIPMENT","TERRY CELL #916-838-0964
FAX #530-865-7455
ANOTHER OFFICE #800-532-2529",|
"00000014","ADVANCED POURED WALL INC","",|
"00000015","ADVANCED PUMPING","",|
"00000027","ACME LIFT COMPANY","NEW #602-254-0650 J.P. FOR ADS
WOODY CELL #602-625-3773.
BARBARA BARBERO - bbarbero@acmelift.com
JON GOES BY JP JON @ 602-708-6216 - EMAIL JON@ACMELIFT.COM
cindilicato@acmelift.com - CARMEN INDILICATO
Statement Printed 12/16/2008
Statement Printed 02/16/2009
Statement Printed 04/20/2009
Statement Printed 10/18/2012
Statement Printed 11/14/2012",|
Log File:
SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jun 23 10:42:51 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Control File: T:\IS\CTLLIB\CTEST1.CTL
Data File: O:\JON\AD5\DBF\CTEST1.CSV
File processing option string: "str '|'"
Bad File: T:\IS\BADLIB\CTEST1.BAD
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 500000
Continuation: none specified
Path used: Direct
Table CUST, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CUST_NO FIRST * , O(") CHARACTER
NAME NEXT * , O(") CHARACTER
NOTES NEXT * , O(") CHARACTER
Record 12: Rejected - Error on table CUST, column NOTES.
Field in data file exceeds maximum length
The following index(es) on table CUST were processed:
index ALCIE.I$CUST_CUST_NO loaded successfully with 11 keys
Table CUST:
11 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.
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 12
Total logical records rejected: 1
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Thu Jun 23 10:42:51 2016
Run ended on Thu Jun 23 10:42:51 2016
Elapsed time was: 00:00:00.47
CPU time was: 00:00:00.04
There are two records in this data set that have notes that wrap lines. The first one (cust_no='00000013') is loaded into the database, no problem. The second one (cust_no='00000027') fails to load as it exceeds maximum length.
Any help is greatly appreciated.
Thanks