Problem with Oracle Sql Loader control file

Question

I'm trying to load some data using sql loader. Here is the top of my control/data file:

LOAD DATA
INFILE *
APPEND INTO TABLE economic_indicators
FIELDS TERMINATED BY ','
(ASOF_DATE DATE 'DD-MON-YY',
VALUE FLOAT EXTERNAL,
STATE,
SERIES_ID INTEGER EXTERNAL,
CREATE_DATE DATE 'DD-MON-YYYY')
BEGINDATA
01-Jan-79,AL,67.39940538,1,23-Jun-2009

... lots of other data lines.

The problem is that sql loader won't recognize the data types I'm specifying. This is the log file:

Table ECONOMIC_INDICATORS, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ASOF_DATE                           FIRST     *   ,       DATE DD-MON-YY       
VALUE                                NEXT     *   ,       CHARACTER            
STATE                                NEXT     *   ,       CHARACTER            
SERIES_ID                            NEXT     *   ,       CHARACTER            
CREATE_DATE                          NEXT     *   ,       DATE DD-MON-YYYY     

value used for ROWS parameter changed from 10000 to 198
Record 1: Rejected - Error on table ECONOMIC_INDICATORS, column VALUE.
ORA-01722: invalid number

... lots of similiar errors, expected if trying to insert char data into a numeric column.

I've tried no datatype spec, all other numeric specs, and always the same issue. Any ideas?

Also, any ideas on why it's changing the Rows parameter?

Answer 1

From your example, SQL*Loader will try to evaluate the string "AL" to a number value, which will result in the error message you gave. The sample data has something looking like it could be a decimal number at third position, not second as specified int he column list.