rrudnik
1/12/2015 - 2:31 PM

Using FORALL UPDATE with EXCEPTIONS

Using FORALL UPDATE with EXCEPTIONS

DECLARE
TYPE T_NUM_REC IS TABLE NUMBER(5);
T_NUM_TYP T_NUM_REC := T_NUM_REC();

BEGIN
FOR i IN 100 .. 105 LOOP
  T_NUM_TYP.extend;
  T_NUM_TYP(T_NUM_TYP.last) := i;
END LOOP;

FORALL j IN 1 .. T_NUM_TYP.COUNT SAVE EXEPTIONS
  UPDATE HR.employees 
  SET EMAIL='test'
  WHERE employee_id = T_NUM_TYP(j);
DBMS_OUTPUT.PUT_LINE(T_NUM_TYP.COUNT);

EXCEPTION
  WHEN OTHERS
  THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
    LOOP
      DBMS_OUTPUT.PUT_LINE('teration number: '||
      SQL%BULK_EXCEPTION (j).ERROR_INDEX
      || ' Error Code: '
      || SQL%BULK_EXceptions (j).ERROR_CODE );
    END LOOP;
END;
/