Load Date Column from foxpro to Oracle

[pranky]

I am trying to load tables from foxpro to oracle using sqlldr. I am doing this in following steps
1. Create .dat file from .dbf using COPY TO command in foxpro
2. Load data from .dat file to oracle using sqlldr

The problem is, foxpro table has date column in which all the rows not necessarily be having data i.e.some date. Some rows may be without date. So first thing is it does not matches date format in foxpro and oracle.In case of such rows i am getting error ' not a valid month ' in logfile after executing sqlldr at dos prompt. And in case of those rows which do not have date in date fields , i get the error ' a nonnumeric character was found where numeric is expecated ' since it comes across value in the form '/ / ' in data file which i am creating from .dbf file.

Can someone give solution to this.

 

[stevecal]

Here's a quick fix: make the date target column in your Oracle a character field then write a quick after update trigger to convert your chars to date...

 

[stevecal]

Sorry, s/b after insert...

 

[ATP]

I've used the nullif command before to get round problems like this.

E.g.

.....
into table tab1
when
(1:7) = '0000001'
( mtri_rec_type position(1:7) char
, mtri_item_id position(8:25) char
, mtri_yr_mth position(26:31) char
, mtri_date position(32:41) date 'DD/MM/YYYY' nullif mtri_date
= BLANKS
, mtri_site_id position(42:49) char
.... etc.