Home » RDBMS Server » Server Utilities » Loading the date value into DATE column (oracle 11g Linux)
Loading the date value into DATE column [message #568997] Thu, 18 October 2012 07:44 Go to next message
9390512774
Messages: 97
Registered: January 2011
Location: hyd
Member
Hi I want to load data from a file using sqlldr.
I have a table
commissions
(
technician_id  char(5)
, tech_name    char(30)
, Comm_rcd_date DATE
, Comm_Paid_date DATE
, comm_amt       number(10,2)
)

my file is
00001,TIMOTHY TROENDLY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0007,123.56
00002,KENNETH KLEMENZ,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0009,123.56
00003,SHUNDAR ARDERY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0005,123.56

please help me to write a ctl file to load this data.

Thanks in Advance

[EDITED by LF: fixed topic title typo; was "aoding"]

[Updated on: Thu, 18 October 2012 12:31] by Moderator

Report message to a moderator

Re: aoding the date value into DATE column [message #568998 is a reply to message #568997] Thu, 18 October 2012 07:46 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
What format is this in?
2011-03-04T01:45:12+0006
Re: Laoding the date value into DATE column [message #568999 is a reply to message #568998] Thu, 18 October 2012 07:59 Go to previous messageGo to next message
9390512774
Messages: 97
Registered: January 2011
Location: hyd
Member
2011-03-04T01:45:12+0006
2011 is year
03 is month
04 is date
01 is hour
45 is mins
12 is sec
Re: Laoding the date value into DATE column [message #569001 is a reply to message #568999] Thu, 18 October 2012 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And +0006?

Regards
Michel
Re: Laoding the date value into DATE column [message #569005 is a reply to message #569001] Thu, 18 October 2012 11:33 Go to previous messageGo to next message
9390512774
Messages: 97
Registered: January 2011
Location: hyd
Member
I don't have the information on the
T
and
+0006


Can I do update type action through the sqlldr concept
Re: Laoding the date value into DATE column [message #569045 is a reply to message #569005] Fri, 19 October 2012 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is almost ISO 8601 date time.

cf. http://www.orafaq.com/forum/m/569044/102589/#msg_569044

Regards
Michel
Re: Laoding the date value into DATE column [message #569096 is a reply to message #569045] Fri, 19 October 2012 18:48 Go to previous message
Barbara Boehmer
Messages: 7988
Registered: November 2002
Location: California, USA
Senior Member
-- test.dat:
00001,TIMOTHY TROENDLY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0007,123.56
00002,KENNETH KLEMENZ,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0009,123.56
00003,SHUNDAR ARDERY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0005,123.56


-- test.ctl:
load data
infile test.dat
into table commissions
fields terminated by ','
trailing nullcols
(technician_id
,tech_name
,comm_rcd_date terminated by '+'
"to_date (substr (:comm_rcd_date, 1, 10)
|| substr (:comm_rcd_date, 12, 8),
'yyyy-mm-ddhh24:mi:ss')"
,fill1 filler
,comm_paid_date terminated by '+'
"to_date (substr (:comm_paid_date, 1, 10)
|| substr (:comm_paid_date, 12, 8),
'yyyy-mm-ddhh24:mi:ss')"
,comm_amt
)


-- table:
SCOTT@orcl_11gR2> create table commissions
  2  (
  3  technician_id  char(5)
  4  , tech_name    char(30)
  5  , Comm_rcd_date DATE
  6  , Comm_Paid_date DATE
  7  , comm_amt       number(10,2)
  8  )
  9  /

Table created.


-- load:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Oct 19 16:43:18 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3


-- results:
SCOTT@orcl_11gR2> column tech_name format a16
SCOTT@orcl_11gR2> select * from commissions
  2  /

TECHN TECH_NAME        COMM_RCD_DATE       COMM_PAID_DATE        COMM_AMT
----- ---------------- ------------------- ------------------- ----------
00001 TIMOTHY TROENDLY 2011-03-04 01:45:12 2011-03-04 01:45:12          7


00002 KENNETH KLEMENZ  2011-03-04 01:45:12 2011-03-04 01:45:12          9


00003 SHUNDAR ARDERY   2011-03-04 01:45:12 2011-03-04 01:45:12          5



3 rows selected.

Previous Topic: exp/imp tables from one tablespace to another
Next Topic: SERVICE NAME vs NET SERVICE NAME
Goto Forum:
  


Current Time: Tue Sep 23 16:22:43 CDT 2014

Total time taken to generate the page: 0.10771 seconds