09-23-04, 18:40 #1Registered User
- Join Date
- Mar 2004
Unanswered: Capturing Errors during processing ( mainly PK and FK constraints )
A follow-on to a question i asked a few days ago :
To capture errors which occur during the loading of a table, mainly FK and PK constraint violations, eg. ORA-00001 and ORA2291, is the only way to do this by using PL/SQL and looping through the source record-set ? I really want to avoid this if possible as I will be loading in buld, 50000 rows at a time on average...
I know i can enable the constraint with a EXCEPTIONS INTO clause, but I take this only applies when first enable the constraint, its not an ongoing thing, therefore my best bet might be to disable the constraint, insert all records, enable it again, and then the errors have been captured and I can do something about it then ? Is this my only option in a high volume bulk load scenario ?
Ideally I would like it to insert all records it can during the bulk load and then capture the error-causing rows and record them somewhere, but of course one violation will cause the entire INSERT SELECT to fail, resulting in 0 rows being inserted... Does anyone have any ideas or workarounds on this, if you could help out it would be much appreciated...
09-23-04, 23:04 #2Registered User
- Join Date
- May 2004
- Dominican Republic
arnzie, try to read about BULK operations in PL/SQL. They are very nice and efficient, and I see they can be benefit for you in this regard.
09-24-04, 08:17 #3Registered User
- Join Date
- Apr 2004
wonderful example, straight from the pl/sql manual:
The following example shows how useful the cursor attribute %BULK_EXCEPTIONS can be:
TYPE NumList IS TABLE OF NUMBER;
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
DELETE * FROM emp WHERE sal > 500000/num_tab(i);
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' ||
END;Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.