OraFAQ Forum: Server Utilities » insert only date using sqlldr

Home » RDBMS Server » Server Utilities » insert only date using sqlldr (oracle 9i)

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

insert only date using sqlldr [message #457177]

Fri, 21 May 2010 09:15

vijay123
Messages: 3
Registered: May 2010

Junior Member

Hi,

I m new to oracle. we are using sqlldr to load data into table.we have data in a csv file as below

"TXN_DATE","TXN_HOUR","VID","HID"
"2010-05-18 20:00:00.0","20","184","212"
"2010-05-18 21:00:00.0","21","184","212"
"2010-05-19 17:00:00.0","17","184","212"

I just want to insert only date without timestamp from the first field TXN_DATE. i.e., i just want 2010-05-18 in my table column.

my table desc is
Name Null? Type
----------------------------------------- -------- ---------
SEQID NUMBER(5)
TXN_DATE NOT NULL DATE
TXN_HOUR NOT NULL NUMBER(2)
VID NOT NULL NUMBER(5)
HID NOT NULL NUMBER(5)

i tried many combination but i couldn't achieve. right now i am able to get only the complete date with timestamp using the following control file.
APPEND INTO PERF_STATS
FIELDS TERMINATED BY ',' optionally ENCLOSED BY '"'
TRAILING NULLCOLS
(
SEQID SEQUENCE(MAX,1),
TXN_DATE timestamp 'yyyy-mm-dd hh24:mi:ss.ff3',
TXN_HOUR integer external,
VID integer external,
HID integer external,
}

Please help me on how to get this date alone from that field.

Thanks,
Vijay

Report message to a moderator

Re: insert only date using sqlldr [message #457178 is a reply to message #457177]

Fri, 21 May 2010 09:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#24433

With EXTERNAL TABLE you can use TRUNC() to zero out time portion.

ALWAYS
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version
or post Oracle version to 4 decimal places

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: insert only date using sqlldr [message #457181 is a reply to message #457178]

Fri, 21 May 2010 10:10

vijay123
Messages: 3
Registered: May 2010

Junior Member

Hi Black Swan,

Sorry for not providing enough details. This is my first post. I will follow your guidelines in future.

I have tried this in my ctl file and it works fine for me.
TXN_DATE "to_date(SUBSTR(:txn_date,1,10),'yyyy-mm-dd')",

Thanks,
Vijay

Report message to a moderator

Re: insert only date using sqlldr [message #457182 is a reply to message #457181]

Fri, 21 May 2010 10:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have tried this in my ctl file and it works fine for me.
>TXN_DATE "to_date(SUBSTR(:txn_date,1,10),'yyyy-mm-dd')",

Congratulations on a fine solution,

Report message to a moderator

Re: insert only date using sqlldr [message #457183 is a reply to message #457181]

Fri, 21 May 2010 10:18

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What is the problem with what you posted?

SQL> select * from PERF_STATS ;

no rows selected

SQL> host type t.ctl
load data
infile *
APPEND INTO TABLE PERF_STATS
FIELDS TERMINATED BY ',' optionally ENCLOSED BY '"'
TRAILING NULLCOLS
(
SEQID SEQUENCE(MAX,1),
TXN_DATE "to_date(SUBSTR(:txn_date,1,10),'yyyy-mm-dd')",
TXN_HOUR integer external,
VID integer external,
HID integer external
)
begindata
"2010-05-18 20:00:00.0","20","184","212"
"2010-05-18 21:00:00.0","21","184","212"
"2010-05-19 17:00:00.0","17","184","212"

SQL> host sqlldr michel/michel control=t.ctl

SQL*Loader: Release 10.2.0.4.0 - Production on Ven. Mai 21 17:18:13 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Commit point reached - logical record count 3

SQL> select * from PERF_STATS ;
     SEQID TXN_DATE              TXN_HOUR        VID        HID
---------- ------------------- ---------- ---------- ----------
         1 18/05/2010 00:00:00         20        184        212
         2 18/05/2010 00:00:00         21        184        212
         3 19/05/2010 00:00:00         17        184        212

3 rows selected.

It works for me.

Regards
Michel

Report message to a moderator

Re: insert only date using sqlldr [message #457196 is a reply to message #457183]

Fri, 21 May 2010 14:09

vijay123
Messages: 3
Registered: May 2010

Junior Member

Hi Michel,

My requirement is to get only the date without timestamp into my table for txn_date column.

Previously i tried with following statement in ym control file.
TXN_DATE timestamp 'yyyy-mm-dd hh24:mi:ss.ff3'
It brings the date with timestamp.

Then i tried with the following statement.
TXN_DATE "to_date(SUBSTR(:txn_date,1,10),'yyyy-mm-dd')",
It inserts only date into the table.

Thanks,
Vijay

Report message to a moderator

Re: insert only date using sqlldr [message #457200 is a reply to message #457196]

Fri, 21 May 2010 15:17

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Don't you see that the time is always 00:00:00 in my table? So it inserts only date.
If you mean that insert a DATE datatype you want only without time, this is not possible Oracle date datatype ALWAYS contains time.

Regards
Michel

Report message to a moderator

Re: insert only date using sqlldr [message #457201 is a reply to message #457200]

Fri, 21 May 2010 15:40

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

Senior Member
Account Moderator

Maybe I'm wrong, but I understood that Vijay said (in the 3rd message in this topic, message #457181) that he solved the problem.

Report message to a moderator

Re: insert only date using sqlldr [message #457222 is a reply to message #457201]

Sat, 22 May 2010 01:16

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Ah! OK, I misunderstood the answer.

Regards
Michel

Report message to a moderator

Previous Topic:	insert data without writing insert statement in oracle
Next Topic:	Found error in DBV Utility

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 14:33:20 CDT 2024