Sorry for the long question, but I would prefer it to be complete.
My goal is to create a process to populate an Oracle 11 Database with data created by (and from) an MS-SQL Server. I would like to load as much data as I can and create an error report on records that fail constraints so we can test the new application with our data and improve our data gathering and repeat this process.
I have NodeJS script that reads directly from the MS-SQL tables (one at a time) and fires INSERT ... EXCEPTION/LOG ERRORS statements to the Oracle database. This seemed to work well until the end. Some tables have 'deferrable constraints' (e.g. parent_id's in same table) that I could not ignore/report so in the end I always end up with a commit error for deferred constraints and 0 inserted rows :(
Note: This question looks is a lot like Continuing Inserts in Oracle when exception is raised however, it does not specify the deferred constraints or other tooling.
Questions given the context (i'm not really an Oracle expert):
1) Is there another way of dealing with those deferred constraints, still getting the valid data inserted and errors logged?
2) Is there better tooling to do this migration and report current data issues?
Well, it sounds like those columns are deferrable for a good reason, because you can't know which rows will fail the constraint until you've inserted all of them.
I'm not sure if it's a best practice, but what I would do is: after inserting all your records into my_table, before you commit, log and delete all the rows that would fail the constraint check, e.g.:
spool bad_rows.txt;
select * from my_table where parent_id not in (select id from my_table);
spool off;
delete from my_table where parent_id not in (select id from my_table);
I can't tell from your question whether this is a one-time data migration, or a regular thing. If you plan to do it often, you might consider creating an exception table to catch the offending rows, then disable the constraints before loading, re-enable them with the EXCEPTIONS clause (and maybe SET CONSTRAINTS ALL IMMEDIATE; to check for violations), then delete the offending rows from the table (using their rowids) before issuing a commit. I think you should be able to automate it fairly well.
I don't do this sort of thing often enough to answer the second part of your question well. I can say that some of your options include (a) using SQL*Loader to do fast direct-path loads, which has pretty good error handling, and (b) creating a remote database link between the MS SQL Server db and the Oracle db, and inserting the data directly, to avoid data formatting issues. Hopefully someone else can provide a better answer about good migration tooling.