Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: Sql Loader issues.

    I have a sql loader that is loading about 20 million rows. I have not used sql control file very often and my Bad File has around 1 million records after load.
    I believe the issue is due to varchar to date conversions causing errors and causing the sql loader to abort with Exit Code 2(Whatever that means, cant be good but i am getting data in my table.)

    My question is what is the best way to handle the varchar to date conversions in a sqlloader? The date data is important but setting it to null is an option if all else fails. Here is my control file.
    (Any and all suggestions appreciated, I don’t understand defaultif or nullif syntax)
    |||
    OPTIONS (ERRORS=10000000, PARALLEL=TRUE, DIRECT=TRUE)
    UNRECOVERABLE
    LOAD DATA
    BADFILE 'RIVER_LICENSE.BAD'

    APPEND
    INTO TABLE DRIVER_LICENSE
    reenable disabled_constraints exceptions DRIVER_TEST
    FIELDS terminated by '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
    (license_number,
    license_type,
    license_issue_date,
    expire_date,
    license_endorsements,
    name,
    mailing_street,
    mailing_city,
    county_or_state,
    zip_code,
    date_of_birth,
    deceased_date,
    age,
    race,
    sex,
    height,
    attention_flag,
    restrictions,
    personal_info,
    updated_on "Sysdate")
    |||

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You need to let oracle know the format of the date(s) in the source file, kinda like this:
    Code:
     ...
    APPEND
    INTO TABLE DRIVER_LICENSE
    reenable disabled_constraints exceptions DRIVER_TEST
    FIELDS terminated by '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
    (
     license_number,
     license_type,
     license_issue_date DATE 'MM/DD/YYYY',
     expire_date        DATE 'MM/DD/YYYY',
     ... Etc ...
     date_of_birth      DATE 'MM/DD/YYYY',
     deceased_date      DATE 'MM/DD/YYYY',
     ... Etc ...
     updated_on "Sysdate"
    )
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •