I am attempting to load data into an Oracle database table (Oracle 11gR2) using sql loader. I am able to load a test file of 1 million records that was sent from our lead company using the control file shown below. However, when they sent the actual files (total of 70 million records) and I tried loading the first file of 1.7 million records which is supposed to be in the exact same format as the test file (and appears to be - text file, comma delimited, same fields), the file will not load using that same control file and I receive the error shown below the Control File. I hope you can help.
Control File
OPTIONS (silent = (feedback, errors, discards, header), direct = true, READSIZE = 20971520 )
unrecoverable load data
infile 'M0000396_Group3_Final.txt' "str ''"
APPEND
into table T3_UNIVERSE
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
( FILE_CODE CHAR(1),
FIPS_STATE_CODE CHAR(2),
ZIP_CODE CHAR(5),
ZIP_PLUS_4 CHAR(4),
DELIVERY_POINT_CODE CHAR(20),
CARRIER_ROUTE CHAR(20),
SURNAME CHAR(65),
ADDRESS CHAR(150),
CITY CHAR(65),
STATE CHAR(2),
ADDRESS_QUALITY_CODE CHAR(2),
ADDRESS_TYPE CHAR(2),
HH_AGE CHAR(2),
HH_AGE_IND CHAR(2),
P1_TITLECODE CHAR(2),
P1_GENDER CHAR(2),
P1_GIVENNAME CHAR(65),
P1_MIDDLEINITIAL CHAR(2),
P1_MS CHAR(2),
P1_MS_IND CHAR(2),
P1_MEMBERCODE CHAR(2),
P1_BIRTHMONTH CHAR(2),
P1_BIRTHYEAR CHAR(4),
P1_PERSONKEY CHAR(20),
P2_TITLECODE CHAR(2),
P2_GENDER CHAR(2),
P2_GIVENNAME CHAR(65),
P2_MIDDLEINITIAL CHAR(2),
P2_MS CHAR(2),
P2_MS_IND CHAR(2),
P2_MEMBERCODE CHAR(2),
P2_BIRTHMONTH CHAR(2),
P2_BIRTHYEAR CHAR(4),
P2_PERSONKEY CHAR(20),
P3_TITLECODE CHAR(2),
P3_GENDER CHAR(2),
P3_GIVENNAME CHAR(65),
P3_MIDDLEINITIAL CHAR(2),
P3_MS CHAR(2),
P3_MS_IND CHAR(2),
P3_MEMBERCODE CHAR(2),
P3_BIRTHMONTH CHAR(2),
P3_BIRTHYEAR CHAR(4),
P3_PERSONKEY CHAR(20),
P4_TITLECODE CHAR(2),
P4_GENDER CHAR(2),
P4_GIVENNAME CHAR(65),
P4_MIDDLEINITIAL CHAR(2),
P4_MS CHAR(2),
P4_MS_IND CHAR(2),
P4_MEMBERCODE CHAR(2),
P4_BIRTHMONTH CHAR(2),
P4_BIRTHYEAR CHAR(4),
P4_PERSONKEY CHAR(20),
P5_TITLECODE CHAR(2),
P5_GENDER CHAR(2),
P5_GIVENNAME CHAR(65),
P5_MIDDLEINITIAL CHAR(2),
P5_MS CHAR(2),
P5_MS_IND CHAR(2),
P5_MEMBERCODE CHAR(2),
P5_BIRTHMONTH CHAR(2),
P5_BIRTHYEAR CHAR(4),
P5_PERSONKEY CHAR(20),
LATITUDE CHAR(20),
LONGITUDE CHAR(20),
LATLONG_IND CHAR(2),
INCOME CHAR(2),
INCOME_IND CHAR(2),
NARROWBAND_INCOME CHAR(2),
HOUSEHOLD_KEY CHAR(20)
)
ERROR Message:
SQL*Loader-510: Physical record in data file (M0000396_Group1_Final.txt) is longer than the maximum(20000000)
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
Table T3_UNIVERSE:
0 Rows successfully loaded.
0 Rows 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:20000000
Total logical records skipped: 0
Total logical records read: 0
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 0
Total stream buffers loaded by SQL*Loader load thread: 0
Edited by: ti3r on May 31, 2013 4:13 PM
I don't see a way to attach a file or I would add that to the post.