| SQL*Loader Syntax [message #401747] |
Wed, 06 May 2009 06:03  |
delbydev
Messages: 2
Registered: May 2009
|
Junior Member |
|
|
Hi
My input row will be as follows
20090507
I wish my destination column (date type) to populated as follows
20090507 23:59:59 (let's park formatting for a moment)
So trying to tag a constant time (hh24:mi:ss) on the end of a date value supplied in the record
Tried the following syntax in the CTL file - to no avail
A)END_DATE || '235959' DATE "YYYYMMDDHH24MISS"
B)END_DATE || 235959 DATE "YYYYMMDDHH24MISS"
Both returned
column END_DATE.
ORA-01861: literal does not match format string
Makes sense - But is there a way of tagging a "Constant" to an inbound value?
Any ideas anyone? - I have worked around it and applied an update to the value after the event - so it is not a showstopper - just would have been nice to keep the upload in one step
Thanks
Derek
|
|
|
|
| Re: SQL*Loader Syntax [message #401778 is a reply to message #401747] |
Wed, 06 May 2009 07:20  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Something like this.
If you are loading large volumes, you can improve the date arithmetic.
cat somectl.ctl
load data
infile *
truncate
into table sometable
FIELDS TERMINATED BY ',' optionally enclosed by '"' trailing nullcols
(
d "(to_date(:d,'YYYYMMDDHH24MISS') - 1/(24*60*60)+1)"
)
begindata
20090507
20090506
20090505
20090504
scott@chum > select * from sometable;
D
-----------------
20090507 23:59:59
20090506 23:59:59
20090505 23:59:59
20090504 23:59:59
Edit:
Or
just use
d "(to_date(:d,'YYYYMMDDHH24MISS') + 86399/(24*60*60))"
[Updated on: Wed, 06 May 2009 07:31] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
