Sql Loader Problem

My table looks like this:

SQL> desc eapp_lic_temp;
Name Null? Type
----------------------------------------- -------- --------------
ACTION_INDICATOR VARCHAR2(200)
COMPANY_CODE VARCHAR2(3)
STATE_CODE VARCHAR2(3)
AGENT_TAX_ID VARCHAR2(16)
LOB_AREA VARCHAR2(60)
LICENSE_TYPE VARCHAR2(1)
LICENSE_NUMBER VARCHAR2(16)
APPT_STATUS VARCHAR2(1)
APPT_EFFECTIVE_DATE DATE
APPT_RENEWAL_DATE DATE
LAST_STATUS_CHANGE_DATE DATE

My controlfile looks like this:


load data
infile 'd:\load\eapp_lic_temp.txt' into table eapp_lic_temp

TRAILING NULLCOLS
(
ACTION_INDICATOR ,
COMPANY_CODE ,
STATE_CODE ,
AGENT_TAX_ID,
LOB_AREA,
LICENSE_TYPE,
LICENSE_NUMBER,
APPT_STATUS,
APPT_EFFECTIVE_DATE "to_date(:APPT_EFFECTIVE_DATE, 'YYYYMMDD')",
APPT_RENEWAL_DATE "to_date(:APPT_RENEWAL_DATE, 'YYYYMMDD')" ,
LAST_STATUS_CHANGE_DATE "to_date(:LAST_STATUS_CHANGE_DATE, 'YYYYMMDD')"

)


The first row of my feed is this:

A 001644823 IL IFCL001644823 PLIF 000000000000000020030305


Iam getting this error:


Record 1: Rejected - Error on table EAPP_LIC_TEMP, column APPT_EFFECTIVE_DATE.
ORA-01840: input value not long enough for date format


Can anyone point out the mistake leading to the above error.

Regards,

K.Diwakar

I think this is because of "0" in the date column...
use DECODE() and find if the values are "0" IF YES the insert NULL ELSE the original data...

HTH

Dear Balaji,

Thanks for you hint. In fact you showed me the direction and it took quite sometime to arrive at the solution (By using the nullif clause in sql-loader).

Thanks and Regards,

K.Diwakar