Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    30

    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...

    Regards,
    Adam

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    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.

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    wonderful example, straight from the pl/sql manual:

    The following example shows how useful the cursor attribute %BULK_EXCEPTIONS can be:

    DECLARE
    TYPE NumList IS TABLE OF NUMBER;
    num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
    errors NUMBER;
    BEGIN
    ...
    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 ' ||
    SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
    END LOOP;
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •