Posted: Thu May 28, 2009 3:32 am
Hi again Maria
I think this is exactly the difference, as you've just witnessed in your scenarios. If the database manager is returning a fatal error, then that will cause your job to abort and you won't achieve capture of these down a reject link. If the errors returned are just SQL warnings of some sort, as far as the database is concerned, then these will be captured and your job will continue. The problem you face with this is that you cannot avoid getting the fatals back, these can't be demoted to warnings or something lesser through the likes of message handling - fatal is fatal.
Though I haven't used the Oracle stages personally (I'm using DB2 in my current assignments), I did notice in the documentation that the Oracle stage has an option of "Disable constraints". Perhaps making use of this you can attempt to load the rows, then when constraints are enabled again after load, you can make use of the exceptions table you define instead. I appreciate this is somewhat messier, but if it fits your requirement to capture any records that failed to load, then it might be your only remaining option.
I think this is exactly the difference, as you've just witnessed in your scenarios. If the database manager is returning a fatal error, then that will cause your job to abort and you won't achieve capture of these down a reject link. If the errors returned are just SQL warnings of some sort, as far as the database is concerned, then these will be captured and your job will continue. The problem you face with this is that you cannot avoid getting the fatals back, these can't be demoted to warnings or something lesser through the likes of message handling - fatal is fatal.
Though I haven't used the Oracle stages personally (I'm using DB2 in my current assignments), I did notice in the documentation that the Oracle stage has an option of "Disable constraints". Perhaps making use of this you can attempt to load the rows, then when constraints are enabled again after load, you can make use of the exceptions table you define instead. I appreciate this is somewhat messier, but if it fits your requirement to capture any records that failed to load, then it might be your only remaining option.