Why aren't my save point and rollback work as expected?

Question

So I have two identical tables called SRC and EMP, I've created a unique index on the ename field of the emp table. What I'm trying to do is copy the employees from the src table into the emp table while respecting the uniqueness condition of the employee name. Problem is It should copy NIKON and one of the two CANON guys... however it only copies NIKON. I am compelled to use a pre-defined exception yet I don't know where the flaw is. I need something like a goto but someone suggested using an exception.

Create Unique Index EIndex ON emp (ename)
Drop table SRC;
Create table SRC (EMPNO NUMBER(4) NOT NULL, ENAME CHAR(10), JOB CHAR(9), MGR NUMBER(4), COPIED NUMBER(1));
Insert into SRC (EMPNO, ENAME, JOB, MGR, COPIED) Values(9001,'NIKON','ANALYST',7902,null);
Insert into SRC (EMPNO, ENAME, JOB, MGR, COPIED) Values (9002,'FORD','ANALYST',7902,null);
Insert into SRC (EMPNO, ENAME, JOB, MGR, COPIED) Values (9003,'CANON','ANALYST',7902,null);
Insert into SRC (EMPNO, ENAME, JOB, MGR, COPIED) Values (9004,'CANON','ANALYST',7902,null);

DECLARE
   Cursor copy_emp_cursor IS
   Select empno, ename, job, mgr from SRC;
   v_record copy_emp_cursor%ROWTYPE;
BEGIN   
OPEN copy_emp_cursor;
LOOP
    Fetch copy_emp_cursor INTO v_record;
    EXIT WHEN copy_emp_cursor%NOTFOUND;
    Savepoint do_insert;
    INSERT INTO emp (empno, ename, job, mgr) values(v_record.empno, v_record.ename, v_record.job, v_record.mgr);
    UPDATE SRC set COPIED = 1 where empno=v_record.empno;
END LOOP;
CLOSE copy_emp_cursor;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK TO do_insert;
    UPDATE SRC set copied = 0 where ename=v_record.ename;
END;

Answer 1

You should handle the DUP_VAL_ON_INDEX exception inside the loop so that the loop can simply continue with the next record. I also recommend that you use a cursor FOR-loop rather than an explicit cursor - it makes for shorter code and allows PL/SQL to perform some optimizations which it can't do if you use an explicit cursor:

BEGIN   
  FOR v_record IN (Select empno, ename, job, mgr from SRC)
  LOOP
    BEGIN
      INSERT INTO emp (empno, ename, job, mgr)
        values(v_record.empno, v_record.ename, v_record.job, v_record.mgr);

      UPDATE SRC set COPIED = 1 where empno=v_record.empno;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE SRC set copied = 0 where ename=v_record.ename;
    END;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Exception: ' || SQLCODE || ' - ' || SQLERRM);
    ROLLBACK;
END;