OraFAQ Forum: Server Utilities » SQL Loader date time conversion problem

Home » RDBMS Server » Server Utilities » SQL Loader date time conversion problem

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

SQL Loader date time conversion problem [message #224540]

Wed, 14 March 2007 11:32

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

Report message to a moderator

Re: SQL Loader date time conversion problem [message #224543 is a reply to message #224540]

Wed, 14 March 2007 11:36

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: SQL Loader date time conversion problem [message #224546 is a reply to message #224540]

Wed, 14 March 2007 11:40

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.

Report message to a moderator

Re: SQL Loader date time conversion problem [message #224547 is a reply to message #224543]

Wed, 14 March 2007 11:41

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

Report message to a moderator

Re: SQL Loader date time conversion problem [message #224548 is a reply to message #224546]

Wed, 14 March 2007 11:43

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

Report message to a moderator

Re: SQL Loader date time conversion problem [message #224549 is a reply to message #224540]

Wed, 14 March 2007 11:45

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: SQL Loader date time conversion problem [message #224554 is a reply to message #224549]

Wed, 14 March 2007 11:55

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

Report message to a moderator

Re: SQL Loader date time conversion problem [message #224575 is a reply to message #224554]

Wed, 14 March 2007 12:45

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

Re: SQL Loader date time conversion problem [message #224601 is a reply to message #224575]

Wed, 14 March 2007 13:47

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

Report message to a moderator

Previous Topic:	export problem
Next Topic:	sqlldr error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 20:43:38 CDT 2024