Home » RDBMS Server » Server Utilities » How to load date filed along with time stamp using sqlldr (orcle 8i unix)
How to load date filed along with time stamp using sqlldr [message #582345] Wed, 17 April 2013 05:53 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>
Re: How to load date filed along with time stamp using sqlldr [message #582359 is a reply to message #582345] Wed, 17 April 2013 07:33 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have many examples in SQL*Loader Case Studies.

Regards
Michel

Previous Topic: impdp not successfull
Next Topic: How to export global temporary tables?
Goto Forum:
  


Current Time: Thu Mar 28 15:38:13 CDT 2024