| How to load date filed along with time stamp using sqlldr [message #582345] |
Wed, 17 April 2013 05:53  |
 |
9390512774
Messages: 103
Registered: January 2011
Location: hyd
|
Senior Member |
|
|
I have table named purchage with 2 columns (order_no number,order_date date) in my database. I want to load the data from a file into that table. The below is the file format
100,4/3/2013 1:18:18 AM
101,4/3/2013 1:18:18 AM
102,4/3/2013 1:18:18 AM
103,4/3/2013 1:18:18 AM
104,4/3/2013 1:18:18 AM
105,4/3/2013 1:18:18 AM
106,4/3/2013 1:18:18 AM
how to load the date filed along with the time stamp.
Thanks in advance.
|
|
|
|
| Re: How to load date filed along with time stamp using sqlldr [message #582347 is a reply to message #582345] |
Wed, 17 April 2013 06:03  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
SQL> desc purchase
Name Null? Type
----------------------------------------------------- -------- ----------------------
ORDER_NO NUMBER
ORDER_DATE DATE
SQL> $type test8.ctl
load data
infile *
into table purchase
replace
fields terminated by ','
trailing nullcols
(
order_no,
order_date "to_date(:order_date, 'dd/mm/yyyy hh:mi:ssam')"
)
begindata
100,4/3/2013 1:18:18 AM
101,4/3/2013 1:18:18 AM
102,4/3/2013 1:18:18 AM
103,4/3/2013 1:18:18 AM
104,4/3/2013 1:18:18 AM
105,4/3/2013 1:18:18 AM
106,4/3/2013 1:18:18 AM
200,4/3/2013 1:18:18 PM
SQL> $sqlldr scott/tiger@ora10 control=test8.ctl log=test8.log
SQL*Loader: Release 11.2.0.2.0 - Production on Sri Tra 17 13:02:50 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 7
Commit point reached - logical record count 8
SQL> select * from purchase;
ORDER_NO ORDER_DATE
---------- -------------------
100 04.03.2013 01:18:18
101 04.03.2013 01:18:18
102 04.03.2013 01:18:18
103 04.03.2013 01:18:18
104 04.03.2013 01:18:18
105 04.03.2013 01:18:18
106 04.03.2013 01:18:18
200 04.03.2013 13:18:18
8 rows selected.
SQL> |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
