[SOLVED] SQL*Loader control file few columns are blank

SQL*Loader control file few columns are blank

My filename .dat file includes 1000 records in below format.

SIC_ID|NAME|CHANGED_DATE|LOGICALLY_DELETED
110|Wheat|31/10/2010 29:46:00|N

Table in which I want to feed content has few more columns. I wish to leave these columns blank as content is not there in .dat file. Table Columns:

SIC_ID, NAME, CREATED_USER_ID ,CREATED_DATE ,CHANGED_USER_ID ,CHANGED_DATE,LOGICALLY_DELETED,RECORD_VERSION

My control file is as below:-

    OPTIONS (DIRECT=TRUE,SKIP=1)
    LOAD DATA CHARACTERSET WE8MSWIN1252
    INFILE "mic_file.dat"
    BADFILE "sql/mic_file.bad"
    REPLACE
    INTO TABLE SDS_SIC
    FIELDS TERMINATED BY "|"
    TRAILING NULLCOLS
    (SIC_ID, NAME,
DATE "DD/MM/YYYY HH24:MI:SS" NULLIF (CHANGED_DATE=BLANKS),
LOGICALLY_DELETED)

After running SQL*Loader, I see below error:

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SIC_ID                              FIRST     *   |       CHARACTER
NAME                                 NEXT     *   |       CHARACTER
CHANGED_DATE                         NEXT     *   |       CHARACTER
LOGICALLY_DELETED                    NEXT     *   |       CHARACTER

Record 1: Rejected - Error on table SDS_SIC, column CHANGED_DATE.
ORA-26041: DATETIME/INTERVAL datatype conversion error

last 2 lines of error is thrown multiple times. This is fixed now :)

Error 2: LOGICALLY_DELETED has only 2 possible values - Y or N.

Record 51: Rejected - Error on table SDS_SIC, column LOGICALLY_DELETED.
ORA-12899: value too large for column LOGICALLY_DELETED (actual: 2, maximum: 1)

Above error is displayed multiple times.

Solution

Remember that the control file column list is in the order fields are in the data file. Data is matched to the table columns by name. Your control file has the 3rd and 4th fields mapped to FILLER, that's why they are blank. FILLER only applies to a field in the data file you don't want.

You need something like this only in your column list section, the TRAILING NULLCOLS will handle the rest of the columns of the table:

(SIC_ID, 
 NAME, 
 CHANGED_DATE  DATE "DD/MM/YYYY HH24:MI:SS" NULLIF (CHANGED_DATE=BLANKS),
 LOGICALLY_DELETED
)

See this recent post which happens to describe the relationship by giving an example: Skipping data fields while loading delimited data using SQLLDR