Unable to get the rejected records from oracle enterprise st

ray.wurlod · Post by **ray.wurlod** » Thu May 14, 2009 4:56 pm

Change the array size to 1 to get correct error reporting.

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Thu May 14, 2009 8:46 pm

Of course your Oracle DBA's may come out and lynch you if the table is really large...

ragunathan13 · Post by **ragunathan13** » Thu May 14, 2009 9:32 pm

I changed the array size as 1 and now it is throwing the below error

Target_Oracle_Entp,0: Open failed for insert:
insert is: <<insert SQL statement >>
sqlcode is: -2290
esql complaint: ORA-02290: check constraint (Target table constraint) violated

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu May 14, 2009 9:48 pm

So now you know what the actual error is. The INSERT statement may suggest which actual CHECK constraint was violated; or there may be more information in the job log or in the Oracle logs.

ray.wurlod · Post by **ray.wurlod** » Fri May 15, 2009 12:04 am

Does your reject link have sqlcode column? Does your reject link or your job have a limit on the number of rows/warnings handled?

ragunathan13 · Post by **ragunathan13** » Fri May 15, 2009 1:21 am

Yes reject link is having sqlcode column, but no limitations for warnings/rows in job as well as reject links.

chulett · Post by **chulett** » Sat May 16, 2009 8:15 am

They're rejected for a reason and that reason is logged, regardless. A 'cheat' would be to demote what you don't want, better to fix the job and (gasp) not violate the constraint. Any reason you can't do that?

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Sat May 16, 2009 11:26 am

rangunathan13,

Let me be a bit more specific about what Craig advised.

The best way to resolve this is to determine what constraints are on the table at a database level and include tests in your job to check each record and route the rejects to a file before they are written to the table.

This would also allow you to set array size back to something more reasonable since you wouldn't need to trap the rejects coming back from the table.

chulett · Post by **chulett** » Sat May 16, 2009 2:16 pm

Exactly - logical rather than physical rejects. No job should ever knowingly violate a constraint on a table and thus should not only be aware of them all but attempt to ensure compliance with them before sending the record to the database.