Save Exception with examples....

SAVE EXCEPTIONS:-

Since version 9 it is possible to do bulk DML using FORALL and use the SAVE EXCEPTIONS clause. It makes sure that all invalid rows - the exceptions - are saved into the sql%bulk_exceptions array, while all valid rows are still processed. This array stores a record for each invalid row, containing an ERROR_INDEX which is the iteration number during the FORALL statement and an ERROR_CODE which corresponds with the Oracle error code.


DECLARE
 USER_EXCE EXCEPTION;
 PRAGMA EXCEPTION_INIT(USER_EXCE,-24381);
 TYPE T1 IS TABLE OF ALL_OBJECTS%ROWTYPE;
 T2 T1;
 CURSOR C1 IS SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=1000;
BEGIN
    OPEN C1;
    LOOP
        FETCH C1 BULK COLLECT INTO T2 LIMIT 100;
        FORALL I IN 1..T2.COUNT SAVE EXCEPTIONS
            INSERT INTO XXC05_OBJECT VALUES T2(I);
        EXIT WHEN T2.COUNT=0;
        END LOOP;
    EXCEPTION
    WHEN USER_EXCE
    THEN
        FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
        LOOP
            DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
    END LOOP;
END;