There are tools in the market that help in executing this phase, like Informatica and Oracle Warehouse Builder (OWB). Sometimes, and for noncomplex ETL tasks, one might choose to run the run SQL script manually.
Whether this is done for the above-mentioned objective or any other objective, ETL could potentially be a time-consuming process especially when the data being loaded is not clean.
Foreign key violation, Primary key violation, missing data, invalid data etc.
When you run SQL script against Oracle database, any of the above violations cause the termination of the script and the rollback of the entire statement. The part that is frustrating is that the Rollback process can talk really long time to complete and undo the changes. You are expected to wait for the rollback to complete, then fix the problem and run again. This time the statement could fail for another violation and you are again expected to repeat the cycle. If you are working with small data sizes, this could be tolerable, but when you are loading millions of records, this could really have an adverse effect on the project.
Oracle 10g Rel 2 offers a new feature that allows you to continue loading your data (albeit of the errors) while keeping the violating rows in a separate ERROR LOG table. This gives you the chance to only examine the violating rows while not having to repeat the same cycle with every error
The following two statement are needed
dbms_errlog.create_error_log('REALSOFT','ERROR_LOG_REALSOFT') ;
ERROR_LOG_REALSOFT REJECT LIMIT UNLIMITED;
I have included an exhaustive and simple example
please visit http://www.e-ammar.net/Oracle.htm
Ammar Sajdi
oraclejo.blogspot.com
www.realsoft-me.com
www.e-ammar.net
