Thread: Sql Loader issues.
03-20-12, 12:10 #1Registered User
- Join Date
- Mar 2012
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 dont understand defaultif or nullif syntax)
OPTIONS (ERRORS=10000000, PARALLEL=TRUE, DIRECT=TRUE)
INTO TABLE DRIVER_LICENSE
reenable disabled_constraints exceptions DRIVER_TEST
FIELDS terminated by '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
03-20-12, 15:14 #2Registered User
- Join Date
- Jun 2003
- West Palm Beach, FL
You need to let oracle know the format of the date(s) in the source file, kinda like this:
... 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