Starting from Oracle database release 10g you can have DML's succeed with partial data failures.
How ?
Default reaction of any bulk Insert or Update to an excption is atomic failure. i.e. If you were inserting 100 rows and one of the row fails to insert due to some constraint violation or duplicate records or any other check stuff. Your whole Insert fails.
Of course you can write a quick PLSQL block with a LOOP and a null exception handler/logger which will basically ignore the exception or write it down somewhere and carry out your next loop iteration insertion.
But first this makes you write the code for a loop and is more resource hogging. You should not have to start coding blocks everytime you want such data insertion.
Below is how you can handle it.
First you need to create a table for error logging it can be in any schema.
Ex.
Once you are done with the table , the next is step straight forward, take your normal insert or update statement and append Log errors statement .
LOG ERRORS INTO TABLE_NAME('LOAD DESC HERE') REJECT LIMIT 25000
So Here's how the example final statement would look. Here Daily_load is just a description for the transaction , you can give anything you want.
Reject Limit : This would be number of acceptable records you are willing to let go in error.
You can give unlimited records in which case DML would be successful even if all records do fail to process all of them would be in your error table of course :) . If the REJECT LIMIT N is crossed and failures exceed N then an exception is thrown and none of the record is inserted.
Similar example with the update .
Happy Coding. :)
How ?
Default reaction of any bulk Insert or Update to an excption is atomic failure. i.e. If you were inserting 100 rows and one of the row fails to insert due to some constraint violation or duplicate records or any other check stuff. Your whole Insert fails.
Of course you can write a quick PLSQL block with a LOOP and a null exception handler/logger which will basically ignore the exception or write it down somewhere and carry out your next loop iteration insertion.
But first this makes you write the code for a loop and is more resource hogging. You should not have to start coding blocks everytime you want such data insertion.
Below is how you can handle it.
First you need to create a table for error logging it can be in any schema.
Ex.
Once you are done with the table , the next is step straight forward, take your normal insert or update statement and append Log errors statement .
LOG ERRORS INTO TABLE_NAME('LOAD DESC HERE') REJECT LIMIT 25000
So Here's how the example final statement would look. Here Daily_load is just a description for the transaction , you can give anything you want.
Reject Limit : This would be number of acceptable records you are willing to let go in error.
You can give unlimited records in which case DML would be successful even if all records do fail to process all of them would be in your error table of course :) . If the REJECT LIMIT N is crossed and failures exceed N then an exception is thrown and none of the record is inserted.
Similar example with the update .
Happy Coding. :)
Comments
Post a Comment
Please leave your relevant comments and questions only.