Getting successfully updated id's from "for all update" in oracle

Question

I am trying to update records in bulk. After running for all update I need to call a procedure and send all successfully updated id's as one of the parameter to the procedure.All those id's for which update was not performed due to some error should not be passed to the procedure. My code is below.

DECLARE
    type emp_type is TABLE OF number;
    EMP_ID EMP_TYPE;
    lv_select varchar2(4000):='select employee_id from EMPLOYEE where dept_NO=1';
BEGIN
    EXECUTE IMMEDIATE lv_select  BULK COLLECT INTO emp_id ;

    FORALL INDX IN 1 ..emp_id.COUNT SAVE EXCEPTIONS  
    UPDATE emp Set salary=salary+1000 
    where employee_id=emp_id(INDX);

    PROC (
    PAR1=>'abc',
    par2=>emp_id(INDX),
    par3=>'xyz'
    );
EXCEPTION
    WHEN OTHERS 
    THEN
    FOR J IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(SQLERRM (-SQL%BULK_EXCEPTIONS (j).ERROR_CODE));
    END LOOP;   
END;

I think you'll need to change your `FORALL` to simple `FOR` loop in order to achieve that. — Gurwinder Singh, Dec 30 '16 at 13:14

score 0 · Accepted Answer · answered Jan 02 '17 at 09:36

This can be simply achieved by using simple for LOOP. Hope below snoippet helps.

  DECLARE
  type emp_type
  IS
    TABLE OF NUMBER;
    EMP_ID EMP_TYPE;
    lv_select VARCHAR2(4000):='select employee_id from EMPLOYEE where dept_NO=1';
  BEGIN
    EXECUTE IMMEDIATE lv_select BULK COLLECT INTO emp_id ;
    FOR indx IN emp_id.first.emp_id.last
    LOOP
      BEGIN
        UPDATE emp SET salary=salary+1000 WHERE employee_id=emp_id(indx);
        PROC ( PAR1=>'abc', par2=>emp_id(INDX), par3=>'xyz' );
      EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(emp_id(indx)|| 'Failed due to '||sqlerrm||'-'||SQLCODE);
      END;
    end loop;
  END;

Getting successfully updated id's from "for all update" in oracle

1 Answers1