Oracle Enterprise Error Trapping

[imbos123]

I am using Oracle Enterpise Edition for inserting records into a table.
I have used the option Upsert so that rejects can be trapped for inserts. I am not updating any records, only doing inserts.
Here is the situation,
When I try to insert records in the table and try to insert two records with the same primary key values it rejects one record and one is inserted into the table. The rejected record is available in the reject file as sqlcode -1. The Director log does not show any error for primary key constraint violation.

But when tere is referential integrity constraint violation. The records are not trapped in reject file. But the job is aborted with FATAL error and it gives the actual oracle error code.sqlcode is: -2291
esql complaint: ORA-02291: integrity constraint. This is shown in Director log.

Now, my requirement is to trap these records in reject files alongwith the error code. And whichever records do not have this error should be loaded in the table. As of now it just aborts the job. But while doing inserts due to primary key violation it gives reject records and the good records are inserted in the table.

Can anyone help me on this...

[Krazykoolrohit]

Change your job desing to capture all duplicate primary keys.
Add a remove duplicate stage with a reject link.

[imbos123]

Change your job desing to capture all duplicate primary keys.
Add a remove duplicate stage with a reject link.

Rohit,

My problem is I am able to get reject records in the reject link or file when there is primary key constraint violation. There is no failure of job. It does not tell me in the Director log that the records are rejected due to Primary key violation. But the reject file records have sqlcode -1 which tells me that it is due to unique key constarint.

When there is a referential integrity constarint the situation is different. There are no records going to reject file. But the job gets aborted showing me FATAL ORA error in the Director.
Now , in the case of referential integrity constaring issue I need the same functionality as I am getting for primary constraints.

I was not able to understand your solution to achieve above said funtionality. Please elaborate..