Save Exception

SAVE EXCEPTIONS with example

SAVE EXCEPTIONS:-

Since version 9i 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;

DECLARE
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 OTHERS
THEN
FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
END LOOP;
END;

Advertisements