TimeStamp in Control File

Question

I have a script that takes a table name and generates a control file by querying all the columns/rows the table. This works fine for numeric and character data but fails on timestamp data so I need to adjust the script to output the timestamp data into the control in such a way that it can be read in properly.

So essentially, my question is how to format TimeStamp data in a control file so that it can be inputed into a TimeStamp column.

Answer 1

You need to use to_date in your column listing as demonstrated here. Something like:

LOAD DATA
INFILE *
INTO TABLE some_table
FIELDS TERMINATED BY ","
(  col1
   col2        "to_date(:col2, 'YYYY-MM-DD HH24:MI:SS')"
)
BEGINDATA
foo,2008-09-17 13:00:00
bar,2008-09-17 13:30:05