Sql Loader Problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Sql Loader Problem

  1. #1
    Join Date
    May 2002
    Posts
    193

    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.

  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.

  3. #3
    Join Date
    May 2002
    Posts
    193
    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
  •  



Click Here to Expand Forum to Full Width