Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Loader combine fields into one.

jefferamFeb 2 2009 — edited Feb 3 2009

Hi i am using sql loader to convert a db from sql server to oracle.
my SQL Server DB has a field for the date and another for the time, i want to convert this into on filed called date. I cannot seem to figure out how to do this.

my ctl file looks like this.

load data
infile '[Cell_Phones].[dbo].[Account_Details].dat' "str '<EORD>'"
into table MD_CELLPHONE.Account_Details
fields terminated by '<EOFD>'
trailing nullcols
(
Account_Number ,
Phone_Number ,
Call_temp FILLER,
Call_Date "TO_DATE(:Call_temp ||' ' ||':Call_Date', 'mm/dd/yyyy HH:miam')"
)

BEGINDATA
3817913.0<EOFD>1234567890.0<EOFD>2007-03-31 00:00:00<EOFD>4:25 PM<EOFD>3817913.0<EOFD>1234567890.0<EOFD>2007-03-24 00:00:00<EOFD>8:19 PM<EOFD>3817913.0<EOFD>1234567890.0<EOFD>2007-03-31 00:00:00<EOFD>4:25 PM<EOFD>

But if i run this i get the following error

SQL*Loader-291: Invalid bind variable CALL_TEMP in SQL string for column CALL_DATE.

basically i cant find a way to do this to combine 2 columns into one and discard the one i am not using.
Any help would be appreciated.
Jeff