Home » RDBMS Server » Server Utilities » SQL Loader Help (Oracle 11g)
SQL Loader Help [message #548163] |
Tue, 20 March 2012 10:07 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/4401d09744d4a856413cf6f0478bb568?s=64&d=mm&r=g) |
StandardOne
Messages: 5 Registered: March 2012
|
Junior Member |
|
|
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 ':DRIVER_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")
|||
|
|
|
|
|
Re: SQL Loader Help [message #548178 is a reply to message #548165] |
Tue, 20 March 2012 11:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/4401d09744d4a856413cf6f0478bb568?s=64&d=mm&r=g) |
StandardOne
Messages: 5 Registered: March 2012
|
Junior Member |
|
|
Here is the table def:
CREATE TABLE DRIVER_LICENSE
(
LICENSE_NUMBER VARCHAR2(13 BYTE),
LICENSE_TYPE VARCHAR2(12 BYTE),
LICENSE_ISSUE_DATE DATE,
EXPIRE_DATE DATE,
LICENSE_ENDORSEMENTS VARCHAR2(5 BYTE),
NAME VARCHAR2(52 BYTE),
MAILING_STREET VARCHAR2(30 BYTE),
MAILING_CITY VARCHAR2(20 BYTE),
COUNTY_OR_STATE VARCHAR2(2 BYTE),
ZIP_CODE VARCHAR2(5 BYTE),
DATE_OF_BIRTH DATE,
DECEASED_DATE DATE,
AGE NUMBER(3),
RACE CHAR(1 BYTE),
SEX CHAR(1 BYTE),
HEIGHT NUMBER(3),
ATTENTION_FLAG VARCHAR2(14 BYTE),
RESTRICTIONS VARCHAR2(5 BYTE),
PERSONAL_INFO CHAR(1 BYTE),
UPDATED_ON DATE
)
(The char to date conversion seems to be implicit)
How can i in the control file state: If error on date set to null, is this possible?
What if i wanted to just set the field "date_of_birth" to null and load the other data, is this possible?
* BlackSwan added {code} tags. Please do so yourself in the future
[Updated on: Tue, 20 March 2012 11:40] by Moderator Report message to a moderator
|
|
|
|
Re: SQL Loader Help [message #548188 is a reply to message #548180] |
Tue, 20 March 2012 12:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
You could write a function to handle all different formats and then use the function in the control file, but it would slow down your load, and with 20M rows it could be significant.
Or just load it into a VARCHAR2 column because the shortsighted people who provided the data are at fault for giving DATE data as a character string.
|
|
|
|
Re: SQL Loader Help [message #548198 is a reply to message #548178] |
Tue, 20 March 2012 13:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/61988.jpg) |
LKBrwn_DBA
Messages: 487 Registered: July 2003 Location: WPB, FL
|
Senior Member |
|
|
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"
)
[Updated on: Tue, 20 March 2012 13:42] by Moderator Report message to a moderator
|
|
|
Re: SQL Loader Help [message #548205 is a reply to message #548180] |
Tue, 20 March 2012 15:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
StandardOne wrote on Tue, 20 March 2012 12:39BlackSwan wrote on Tue, 20 March 2012 10:20>I believe the issue is due to varchar to date conversions causing errors
above could be true, but then it would mean that "dates" exist in more than one format;
since many rows get loaded without error.
Unfortunately some of the date data is in different formats. Is there anyway to tell the loader if one field errors, set to null?
Now that I re-read your remark, I ask you are the dates in the same column in different formats or does each column have a different date format?
If it's the latter, then follow LKBrwn_DBA advice, otherwise his advice will do you no good if it is the former.
|
|
|
|
Re: SQL Loader Help [message #548368 is a reply to message #548208] |
Wed, 21 March 2012 11:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/4401d09744d4a856413cf6f0478bb568?s=64&d=mm&r=g) |
StandardOne
Messages: 5 Registered: March 2012
|
Junior Member |
|
|
Littlefoot wrote on Tue, 20 March 2012 15:41StandardOneWhat if i wanted to just set the field "date_of_birth" to null and load the other data, is this possible?
Just omit it from the control file.
That might be OK if you are sure that only DATE_OF_BIRTH values are invalid, but - there are 5 DATE datatype columns. What good would it do if you insert NULL into all of them?
Anyway: I vote for a function too. Try to handle as many possible "formats" people entered into these columns in order to detect them. It is OK if you start with just one; load some records (not 20 million! Let's say a thousand or so), see which ones failed, add them into the function, reload. Repeat the process until all records are loaded and then try with a larger data set.
Once you are done and function works nicely, remember us and post it here. A lot of future posters will be grateful.
I spent the better part of the day yesterday working on this. I explicitly set the date format and it made no difference. Out of the 20 million records, 5 million were failing.(Not good) I created a dummy file with only 20,000 records in the file and ran it and about the same, 5000 were failing.
At this point, i sql loaded one column at a time, when it would complete with all the records, i added the next column.
Example:
FIELDS terminated by '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
license_number
)
Then
FIELDS terminated by '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
license_number,
LICENSE_TYPE
)
The erroring column was the second to last column(Not the dates at all)
PERSONAL_INFO
Which i set to null like this:
PERSONAL_INFO "null",
Once i did that all records ran successfully. Only one record failed compared to 5 million.
I would like to again thank everyone for there assistance on this as i was new to sql loaders and i learned quite a bit.
|
|
|
Re: SQL Loader Help [message #548377 is a reply to message #548368] |
Wed, 21 March 2012 12:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Bah! So we were wrong from the beginning! Never mind, I'm glad you fixed it. Thank you for the feedback!
|
|
|
Goto Forum:
Current Time: Sat Sep 07 20:12:07 CDT 2024
|