Home » RDBMS Server » Server Utilities » SQL Loader date time conversion problem
SQL Loader date time conversion problem [message #224540] Wed, 14 March 2007 11:32 Go to next message
ejoerden
Messages: 5
Registered: March 2007
Location: Germany
Junior Member
Hi,

got a field terminated file including data like the following:

"2007-03-14","10:00:00","12:00:00"

meaning action date, starttime, endtime

Need to get this into a table with columns

actionstart date
actionend date

Been trying with filler, ref and else for a whole day now
but can't get it done.

Anybody got a solution?

Tnx
Ewald



Re: SQL Loader date time conversion problem [message #224543 is a reply to message #224540] Wed, 14 March 2007 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Anybody got a solution?
IMO, the design is seriously flawed.
What happens if/when end date is different from start date?
It appears at a minimum you'll need to load into an intermediate table & the perform some minor ETL to get into the format desired.
Re: SQL Loader date time conversion problem [message #224546 is a reply to message #224540] Wed, 14 March 2007 11:40 Go to previous messageGo to next message
aciolac
Messages: 242
Registered: February 2006
Senior Member
You can simply load this dates into a table(for example with 3 varchar2 columns) by SQLLoader, and reformat them after, with SQL functions. Also in UNIX You can use some combination of awk and SQLLoader and load dates directly as You need.
Re: SQL Loader date time conversion problem [message #224547 is a reply to message #224543] Wed, 14 March 2007 11:41 Go to previous messageGo to next message
ejoerden
Messages: 5
Registered: March 2007
Location: Germany
Junior Member
There's no flaw as the datasource ist absolutly trustable.
Don't need to check for correctness, just have to fill up
the database with some n million rows as fast as possible.

Ewald
Re: SQL Loader date time conversion problem [message #224548 is a reply to message #224546] Wed, 14 March 2007 11:43 Go to previous messageGo to next message
ejoerden
Messages: 5
Registered: March 2007
Location: Germany
Junior Member
Hi aciolac,

this is a matter of time. The import has to be done as fast as
possible so I'm looking for a solution to do this directly
in SQL Loader

Ewald
Re: SQL Loader date time conversion problem [message #224549 is a reply to message #224540] Wed, 14 March 2007 11:45 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
What happens if/when end DATE is different from start DATE?
you are entitled to your delusions & might think differently when reality reveals the folly of what has been described.
What happens if/when end DATE is different from start DATE?
With only a single date in the input, problems will result when your event starts just before midnight & completes after midnight.
What happens if/when end DATE is different from start DATE?
Re: SQL Loader date time conversion problem [message #224554 is a reply to message #224549] Wed, 14 March 2007 11:55 Go to previous messageGo to next message
ejoerden
Messages: 5
Registered: March 2007
Location: Germany
Junior Member
Hi anacedent,

the date to be loaded will come from another applications database where it's alreay checked for consistency.
If there were consistency problems the developers will have to
deal with it. I simply have to make the data available to them in the fastest possible way.

Ewald
Re: SQL Loader date time conversion problem [message #224575 is a reply to message #224554] Wed, 14 March 2007 12:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you try with boundfiller?

http://www.orafaq.com/forum/m/170602/0/?srch=boundfiller#msg_170273
Above URL will give you an example.
Only differences are
1. you are dealing with dates. So apply proper date formatting and add date with time.
2. In the control file, specify the fields like this
(
c1_in_table " :field1_in_datafile + :field2_in_datafile ",
c2_in_table " :field1_in_datafile + :field3_in_datafile ",
field1_in_datafile boundfiller,
field2_in_datafile boundfiller,
field3_in_datafile boundfiller
)


Right now, i have no access to database, else i would given it a try.
Re: SQL Loader date time conversion problem [message #224601 is a reply to message #224575] Wed, 14 March 2007 13:47 Go to previous message
ejoerden
Messages: 5
Registered: March 2007
Location: Germany
Junior Member
Hi Mahesh,

tnx a lot, you pushed me to the right lane Razz

I didn't realise the difference between boundfiller and filler
so my tries always ended with "illegal bind variable" Embarassed

To complete this thread for later readers:
With delimeted files you have to put your concatenated columns
to the end of your field list and use TRAILING NULLCOLS as
this fiels are not part of the actual record in the input field.

Ewald
Previous Topic: export problem
Next Topic: sqlldr error
Goto Forum:
  


Current Time: Sat Dec 03 07:53:18 CST 2016

Total time taken to generate the page: 0.08664 seconds