OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL Loader Help (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader Help [message #548163]

Tue, 20 March 2012 10:07

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")
|||

Report message to a moderator

Re: SQL Loader Help [message #548165 is a reply to message #548163]

Tue, 20 March 2012 10:16

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You aren't handling CHAR -> DATE conversion at all ...

Could you, along with the control file, post table description and several sample records?

Report message to a moderator

Re: SQL Loader Help [message #548167 is a reply to message #548165]

Tue, 20 March 2012 10:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>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.

Report message to a moderator

Re: SQL Loader Help [message #548178 is a reply to message #548165]

Tue, 20 March 2012 11:37

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 #548180 is a reply to message #548167]

Tue, 20 March 2012 11:39

StandardOne
Messages: 5
Registered: March 2012

Junior Member

BlackSwan 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?

Report message to a moderator

Re: SQL Loader Help [message #548188 is a reply to message #548180]

Tue, 20 March 2012 12:33

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.

Report message to a moderator

Re: SQL Loader Help [message #548194 is a reply to message #548163]

Tue, 20 March 2012 13:11

StandardOne
Messages: 5
Registered: March 2012

Junior Member

Thank you Joy_Division, BlackSwan, and LittleFoot for your replies. I will try some explicit conversion using to_date and see if i can use a decode statement to achieve success.

If this does not work i will need to change the field type to varchar2 and rewrite my procedures. Thanks for your time.

Report message to a moderator

Re: SQL Loader Help [message #548198 is a reply to message #548178]

Tue, 20 March 2012 13:27

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

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

StandardOne wrote on Tue, 20 March 2012 12:39

BlackSwan 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.

Report message to a moderator

Re: SQL Loader Help [message #548208 is a reply to message #548178]

Tue, 20 March 2012 15:41

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

StandardOne

What 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.

Report message to a moderator

Re: SQL Loader Help [message #548368 is a reply to message #548208]

Wed, 21 March 2012 11:20

StandardOne
Messages: 5
Registered: March 2012

Junior Member

Littlefoot wrote on Tue, 20 March 2012 15:41

StandardOne
What 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.

Report message to a moderator

Re: SQL Loader Help [message #548377 is a reply to message #548368]

Wed, 21 March 2012 12:30

Littlefoot
Messages: 21823
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!

Report message to a moderator

Previous Topic:	sql loader skip blank line
Next Topic:	External Table cannot be viewed....

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 17:09:33 CST 2025