Home » RDBMS Server » Server Utilities » SQL*Loader Syntax (Oracle 11.1 on Solaris)
SQL*Loader Syntax [message #401747] Wed, 06 May 2009 06:03 Go to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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

Re: SQL*Loader Syntax [message #401787 is a reply to message #401747] Wed, 06 May 2009 07:34 Go to previous message
delbydev
Messages: 2
Registered: May 2009
Junior Member
Nice

Thanks

Case closed - should have thought of that myself Laughing
Previous Topic: Import/Export data from One databse to another database
Next Topic: How to call Oracle sqlldr in Java
Goto Forum:
  


Current Time: Wed Dec 07 14:26:34 CST 2016

Total time taken to generate the page: 0.08113 seconds