capturing Rejections from Oracle enterprise stage

[Aquilis]

Hi all,

I need to capture the rejections at Database level, so i have a design like:

Oracle enterprise(source DB)----->Transformer------------------>Oracle enterprise stage(target DB)---------> Reject DB table

Datatypes for all the columns At source DB level are VARCHAR But target DB has got all DATE,NUMBER And CHAR Datatypes.

I am trying to catch the ErrorCodes generated from the oracle enterprise stage at target Database.So i need to validate for date & Number values at database level.But my source table,has got Character data and that should be loaded to the columns with DATE & NUMBER datatype columns in target table.
Ex:
source table has metadata like:
Column1.......varchar
Column2.........varchar

and data as :
column1 |column2
----------------------
abc|xyz
xxxf|asjkf

target table has metadata like:
Column1.......Date
Column2.........Number

i am not doing any validations in Transformer stage,but i want all these character data should be rejected from target Database(DB level rejections).
when i load the data to the Number column(i.e column2) it will load as value '0' (zero) as a default value. And for date column(i.e Column2) ,if i load character data then job will get aborted.what i was thinking ,character data should be rejected into the reject table(from Target DB with autogenerated oracle ErrorCode).

so is it possible to capture Validation failure at Db level for Date column or shall collect rejections from transformer ,but the issue will be i loose error code.


appreciate everybody for your inputs.

aquilis

[sud]

You could definitely catch rejections at the database stage(search the forum this has been discussed many times). Alternatively, you could do explicit type checks in transformer and you should not need the ORA error codes since you will know which check failed.

[sachin1]

in oracle OCI stage you have input tab in which you will find "Treat warning as fata error" and with this in tranformer you use rejected link. you will get all error codes for oracle.

[Aquilis]

in oracle OCI stage you have input tab in which you will find "Treat warning as fata error" and with this in tranformer you use rejected link. you will get all error codes for oracle.

thanks for the response:

But i am moving with oracle enterprise stage.What I want know is ,will it be possible to capture the datatype mismatch data into the reject table.

Currently i am loading a varchar value="ABCD" into the DATE("YYYYMMDD") column in the target .
My job is getting aborted and encountering error message as:

sqlcode is: -1841
esql complaint: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
.
What i am expecting is : this record should be rejected into the reject table from oracle enterprise stage(target). I am trying to collect rejection from oracle enterise stage but not from Transformer stage (as mentioned in dataflow previously) .

[chulett]

Have you not added a reject link from your Oracle Enterprise stage to wherever you want to collect them?

[Aquilis]

Have you not added a reject link from your Oracle Enterprise stage to wherever you want to collect them? ...

Chulet,

I have added the reject link and am able to collect unique constraint violated data into the reject link.But i am facing the following issues:

1. Not able to collect the Not Null constraint violated data .But the data holding Null value is being dropped which should be moved to reject link as per exepctation.
2.Not able to collect the Bad Data(character data) entering into the Date column of target database, actually which should be rejected to reject link. for this job is being aborted.
3. Not able collect the Character data entering into the Number column of th target database. but this data is loading into the Target with a default value as zero which seems to funny as No such a default value is set for that column.

When i am able to collect the Unique constarint violated data into reject link,then why can't I not able to move all other rejections.

Hearty Thanks for till now support,

[chulett]

AFAIK, a reject link should handle anything that is rejected by the database - in other words that generates an ORA error. This will not happen silently, if that's what you are expecting, all those warnings will still be logged and if you log more than the Warning Limit for that run the job will abort. Or if for some reason you log other fatal messages, the job will abort.

You'd have to be more precise reporting why 'this job is being aborted'. What actually cause the abort? Simply saying 'it aborted' tells us nothing.

If you feel the stage you are using is not behaving correctly or is doing something funny, open a case with your official Support provider. Ask them how it should be working, they can help you ascertain if something is indeed wrong with either the way it is working or what you are doing. They get a nice slice of your pie for providing that service, take advantage of it!

[manojbh31]

[quote="chulett"]AFAIK, a reject link should handle anything that is rejected by the database - in other words that generates an ORA error. This will not happen silently, if that's what you are expecting, all those warnings will still be logged and if you log more than the Warning Limit for that run the job will abort.


Hello Everyone,
As chulet mentioned,its right to maximum extent.
Because I too have encountered with the same issue while datatype coversion from varchar to date.
Oracle Enterprse stage interpret it as a fatal Error and job will get aborted.
You can find something more about this oracle enterprise stage at link :

http://wiki.ittoolbox.com/index.php/Par ... led_upsert

In my case it was generating only 'sqlcode' .Actually it should generate both sqlcode and sqlstate.And it was not even allowing Null values rejected while inserting into NotNull column,this record was being dropped.
So i changed the idea of collecting rejects from oracle enterprise stage.
if you have any updated information regarding this, do share with us.

[JoshGeorge]

I think the same topic is available in FAQ section by the same Author (Vincent).
Refer this LINK