OraFAQ Forum: Server Utilities » Loading the date value into DATE column

Home » RDBMS Server » Server Utilities » Loading the date value into DATE column (oracle 11g Linux)

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

Loading the date value into DATE column [message #568997]

Thu, 18 October 2012 07:44

9390512774
Messages: 103
Registered: January 2011
Location: hyd

Senior 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

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

What format is this in?

2011-03-04T01:45:12+0006

Report message to a moderator

Re: Laoding the date value into DATE column [message #568999 is a reply to message #568998]

Thu, 18 October 2012 07:59

9390512774
Messages: 103
Registered: January 2011
Location: hyd

Senior 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

Report message to a moderator

Re: Laoding the date value into DATE column [message #569001 is a reply to message #568999]

Thu, 18 October 2012 08:35

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And +0006?

Regards
Michel

Report message to a moderator

Re: Laoding the date value into DATE column [message #569005 is a reply to message #569001]

Thu, 18 October 2012 11:33

9390512774
Messages: 103
Registered: January 2011
Location: hyd

Senior Member

I don't have the information on the T
and +0006

Can I do update type action through the sqlldr concept

Report message to a moderator

Re: Laoding the date value into DATE column [message #569045 is a reply to message #569005]

Fri, 19 October 2012 01:13

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is almost ISO 8601 date time.

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

Regards
Michel

Report message to a moderator

Re: Laoding the date value into DATE column [message #569096 is a reply to message #569045]

Fri, 19 October 2012 18:48

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 21:58:07 CST 2025