Conversion of null and invalid values in Oracle Enterprise

[bgs_vb]

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 .

[ray.wurlod]

Easiest would be a constraint expression based on IsValid() function.

[bgs_vb]

Easiest would be a constraint expression based on IsValid() function. ...

Hi Ray,
I am using reject file associated with Oracle Enterprise Stage and don't want to use two reject files one with transformer and other with Oracle Enterprise stage also .

Is this acceptable behaviour of DataStage that it loads invalid date format to a default value instead of aborting or reject the record?

Also please advise how can i achive this record rejection with Oracle Enterprise stage reject .

[ray.wurlod]

No idea. I'd prefer to prevent them being sent to Oracle in the first place. Hence my suggested approach.