Job not aborting in case of primary key violation error

[shalini11]

Hi All,

The requirement is to insert records into a table from a file. Before inserting into table I have to get the maximum value of the primary key. Then i have to append the records into table incrementing the value each time.

For eg: The table structure is:

Col1 Col2
00000 unknown
00001 gasoline
00002 petrol

Col1 is primary key.

now i have to insert into it the value diesel after petrol with value for Col1 as 00003

if I try to insert a value with the primary key already existing in the table, the job is not aborting. The value is getting inserted and the primary index on the table becomes usable. In the target Oracle Enterprise stage ,I am using Index mode as Rebuild.

What should I do so that the job aborts if the records are violating the primary key constraints?

Thanks

[ray.wurlod]

Add a reject link to handle the row rejected by the database, and set the row limit on this link to 1.

[shalini11]

I added the reject link to OE stage but where to set row limit to 1

[ray.wurlod]

In a TRANSFORMER stage downstream on the reject link.

[shalini11]

Now the job is getting aborted if I am trying to insert records with duplicate values for the primary key into a tablebut the records are getting inserted and auto committed. And the primary constraint becomes disabled.

What should I do so that in case of primary key violation, the records should not be inserted into table i.e rollback should occur. Is it possible in DS?

I am using Oracle Enterprise stage

Thanks in adv