Conversion of null and invalid values in Oracle Enterprise
Posted: Thu Dec 09, 2010 3:19 am
Hi ,
I am getting source adta as pipe delimiter format . It has a date field in dd/mm/yyyy format . So in seq file i defined it as varchar and in transformer i am applying StringToDate function to convert this into date field and loading to Oracle Enterprise Stage .
The issue is if in particular date column we are getting invalid date i.e 21/35/2010 then the job is not aborting neither Oracle enterprise is generating rejects ,instead it is loading that record with date value as null value .Similiarly if source has null value in the date field its loading 01/01/0001 in the Oracle table .
This prooblem is not only with datefields ,its defaulting the values as 0 for numeric not null fields also .
Please suggest how can we avoid this , Ideallt its hould reject these records or raise a fatal error .
I am getting source adta as pipe delimiter format . It has a date field in dd/mm/yyyy format . So in seq file i defined it as varchar and in transformer i am applying StringToDate function to convert this into date field and loading to Oracle Enterprise Stage .
The issue is if in particular date column we are getting invalid date i.e 21/35/2010 then the job is not aborting neither Oracle enterprise is generating rejects ,instead it is loading that record with date value as null value .Similiarly if source has null value in the date field its loading 01/01/0001 in the Oracle table .
This prooblem is not only with datefields ,its defaulting the values as 0 for numeric not null fields also .
Please suggest how can we avoid this , Ideallt its hould reject these records or raise a fatal error .