-
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
Last edited by diwakar; 09-16-2003 at 10:09 AM.
-
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
Cheers!
OraKid.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|