Did you know? Running Oracle blocks, trapping rejects &
Posted: Fri May 28, 2004 5:52 pm
We have ben trying to come up with a solution to trap rejects... our requirements were...
1. Abort the job on 1st record reject, but trap the rejected record before aborting (on couple of jobs)
2. Trap all types of rejects (not only NOT NULLs, but even referential etc) but don't abort the job (on couple of other jobs)
So, here is how we did it...
1. In the user-defined SQL, we wrapped the SQL in a begin....end block and basically added in an EXCEPTION handler within the block
2. Within the exception handler, as you all know, we can handle the different oracle thrown errors in the way we want to, you can insert the rejected record onto a table or do something else
3. If you want to abort, you can put in a raise application error, which basically tells datastage that it is a fatal error and so it ABORTs, but before you do this, you would have obviously pushed the rejected record into a separate table
This works pretty neatly and best-of-all gives us the power to handle DB rejects in the way we want to.
And, of course, since Datastage seems to be fine with passing these blocks to the database, we can do much more (whoever knows PL/SQL will obviously like this)
Just thought of sharing this with everybody
1. Abort the job on 1st record reject, but trap the rejected record before aborting (on couple of jobs)
2. Trap all types of rejects (not only NOT NULLs, but even referential etc) but don't abort the job (on couple of other jobs)
So, here is how we did it...
1. In the user-defined SQL, we wrapped the SQL in a begin....end block and basically added in an EXCEPTION handler within the block
2. Within the exception handler, as you all know, we can handle the different oracle thrown errors in the way we want to, you can insert the rejected record onto a table or do something else
3. If you want to abort, you can put in a raise application error, which basically tells datastage that it is a fatal error and so it ABORTs, but before you do this, you would have obviously pushed the rejected record into a separate table
This works pretty neatly and best-of-all gives us the power to handle DB rejects in the way we want to.
And, of course, since Datastage seems to be fine with passing these blocks to the database, we can do much more (whoever knows PL/SQL will obviously like this)
Just thought of sharing this with everybody