Question: Let's say you are doing a bulk operation of the type:
...
FETCH my_cursor BULK COLLECT INTO my_collection;
...
FORALL i IN 1..my_collection.COUNT
INSERT INTO my_table my_collection(i).value;
And some exception occurs, that is the code aborts at some point. Is there a way to save this?
Answer: The SAVE EXCEPTIONS clause will record any exception during the bulk operation, and continue processing.
...
some_number NUMBER;
...
FORALL i IN 1..my_collection.COUNT SAVE EXCEPTIONS
INSERT INTO my_table my_collection(i).value;
...
EXCEPTION
WHEN OTHERS THEN
some_number := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..some_number LOOP
DBMS_OUTPUT.PUT_LINE('Error ' || i || ', iteration ' ||
SQL%BULK_EXCEPTIONS(i).error_index || ' is: ' ||
SQLERRM(0 - SQL%BULK_EXCEPTIONS(i).error_code));
END LOOP;
...
FETCH my_cursor BULK COLLECT INTO my_collection;
...
FORALL i IN 1..my_collection.COUNT
INSERT INTO my_table my_collection(i).value;
And some exception occurs, that is the code aborts at some point. Is there a way to save this?
Answer: The SAVE EXCEPTIONS clause will record any exception during the bulk operation, and continue processing.
...
some_number NUMBER;
...
FORALL i IN 1..my_collection.COUNT SAVE EXCEPTIONS
INSERT INTO my_table my_collection(i).value;
...
EXCEPTION
WHEN OTHERS THEN
some_number := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..some_number LOOP
DBMS_OUTPUT.PUT_LINE('Error ' || i || ', iteration ' ||
SQL%BULK_EXCEPTIONS(i).error_index || ' is: ' ||
SQLERRM(0 - SQL%BULK_EXCEPTIONS(i).error_code));
END LOOP;
Comments
Post a Comment