Oracle: Changing a column using SQL*Loader

Our project requires that data in one of our tables need to be summarized first before inserting into the new tables. Data that are loaded into the old table, summarized, then inserted into the new ones. While looking into the script that loads into the old table, we saw that it loads the data first using SQL*Loader, then updates a date column based on a character column. Let’s call them col_date and col_char, respectively.
This can be achieved using SQL*Loader and can improve the process time. All we did was to change the attribute of that column. For example, here’s what the old control file looks like:


LOAD DATA
APPEND INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
col_char,
col_date
)
What we did was to change the col_date field. This is what we ended with:


LOAD DATA
APPEND INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
col_char,
col_date "to_date(:col_char,'MMDDYY')"
)
Basically you can change any column using built-in Oracle functions. Let’s say you want to make the col_date yesterday’s date, then the control file would be:


LOAD DATA
APPEND INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
col_char,
col_date "trunc(sysdate – 1)"
)
Oracle’s buil- in functions can be easily searched using Google.