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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a8150cc192dcf065490e90b93a5cbda9?s=64&d=mm&r=g) |
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: 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 Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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.
|
|
|
Goto Forum:
Current Time: Sun Jun 30 23:47:29 CDT 2024
|