Capturing rejected rows in Oracle

major · Post by **major** » Tue Aug 24, 2010 10:59 am

Hi,

I am trying to insert data in a Oracle db using Oracle OCI stage.
I need to capture the records which failed to insert in the table.

So I have created a new link and marked the link as rejected and in transformer i have put a constraint like oracletable_linkname.REJECTED on the rejected link.

but the rejected data is not coming into the link though the job is throwing a warning message.

But the same approach is working fine when i use for Sybase or Sql db.

I have searched before posting this.

Can anyone please help me on this

Thanks in advance.

Sreenivasulu · Post by **Sreenivasulu** » Tue Aug 24, 2010 5:32 pm

If the record is getting rejected due to null values and if you are redirecting this to a sequential file then it will not load to this file. This happens as a sequential file cannot be loaded with null values
You can use a MODIFY stage to change the null values to some default value. This will then enable the records to go to the sequential file

Regards
Sreeni

chulett · Post by **chulett** » Tue Aug 24, 2010 7:38 pm

There's no MODIFY stage in a Server job... that functionality would need to be done in a Transformer.

major · Post by **major** » Wed Aug 25, 2010 3:16 am

Hi Guys,

But my input doesn't contain any null values and my rejected data is due to 'Unique key violation' constraint defined on the Oracle table.

Thanks,

chulett · Post by **chulett** » Wed Aug 25, 2010 6:19 am

Make sure the link order is correct - that your Reject link is last - and try removing the constraint. Just let the fact that it is marked as a Reject row do the work and see if that helps.

major · Post by **major** » Wed Aug 25, 2010 6:53 am

Chulett,

Tried that option also by making sure Reject is the last link , but of no use.

Thanks

chulett · Post by **chulett** » Wed Aug 25, 2010 7:02 am

OK, let's go the opposite direction then. Do not mark the link as a Reject link and restore your 'LinkName.REJECTED' constraint value. Make sure you pick that from the Helper rather than type it in manually.

Both of those approaches should work just fine. If they're not, we're missing something important. What Array Size value are you using in the target stage? It will need to be set to 1 if you want your Reject mechanism to work properly.

jdsmith575210 · Post by **jdsmith575210** » Sat Aug 28, 2010 10:19 am

Have you tried setting the Array Size to 1 on your target stage?

chulett · Post by **chulett** » Sat Aug 28, 2010 10:22 am

Excellent question and one I've already asked but we haven't heard back yet. I think that is what will straighten this out.

major · Post by **major** » Sat Aug 28, 2010 12:23 pm

Sorry guys i am off for few days. i am not sure whether i set the transactions array size to 1 or not.
Will check that on this Monday and update you

Thanks

chulett · Post by **chulett** » Sat Aug 28, 2010 2:53 pm

Don't confuse the two - transaction size and array size are two different things. We're talking about the latter.

major · Post by **major** » Mon Aug 30, 2010 7:46 am

chulett wrote:Don't confuse the two - transaction size and array size are two different things

Got it Chulett

And guys finally setting transaction size to 1 solved my issue.

Thanks,

jdsmith575210 · Post by **jdsmith575210** » Mon Aug 30, 2010 11:20 am

Just so it's clear, setting the Transaction Size to 1 will give you what you want, but can also severly reduce the speed of your job.