Home » RDBMS Server » Server Utilities » insert only date using sqlldr (oracle 9i)
insert only date using sqlldr [message #457177] Fri, 21 May 2010 09:15 Go to next message
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
Re: insert only date using sqlldr [message #457178 is a reply to message #457177] Fri, 21 May 2010 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 26757
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/
Re: insert only date using sqlldr [message #457181 is a reply to message #457178] Fri, 21 May 2010 10:10 Go to previous messageGo to next message
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


Re: insert only date using sqlldr [message #457182 is a reply to message #457181] Fri, 21 May 2010 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 26757
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,
Re: insert only date using sqlldr [message #457183 is a reply to message #457181] Fri, 21 May 2010 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 67653
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
Re: insert only date using sqlldr [message #457196 is a reply to message #457183] Fri, 21 May 2010 14:09 Go to previous messageGo to next message
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
Re: insert only date using sqlldr [message #457200 is a reply to message #457196] Fri, 21 May 2010 15:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67653
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
Re: insert only date using sqlldr [message #457201 is a reply to message #457200] Fri, 21 May 2010 15:40 Go to previous messageGo to next message
Littlefoot
Messages: 21648
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.
Re: insert only date using sqlldr [message #457222 is a reply to message #457201] Sat, 22 May 2010 01:16 Go to previous message
Michel Cadot
Messages: 67653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! OK, I misunderstood the answer.

Regards
Michel
Previous Topic: insert data without writing insert statement in oracle
Next Topic: Found error in DBV Utility
Goto Forum:
  


Current Time: Mon Jan 25 01:16:31 CST 2021