--
-- stored procedure takes 2 input.
-- empid to be updated and newempid
--
mag@itloaner1_local > create or replace procedure sp_test345 (oldempid in number,
  2                                     newempid in number)
  3  as
  4  begin
  5  update test345 set empid=newempid where empid=oldempid;
  6  commit;
  7  exception
  8     when dup_val_on_index then
  9     raise_application_error(-20000,'the empid '||newempid||' already exists');
 10  end;
 11  /

Procedure created.
--
-- inserting sample rows sequentially from 1 to 25
--
mag@itloaner1_local > begin
  2  for mag in 1..25 loop
  3   insert into test345 values (mag);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.
--
-- updating using proceudre
-- empid 3 is updated to 5.
-- empid 5 already exists..so error is displayed.

mag@itloaner1_local > exec sp_test345(3,5);
BEGIN sp_test345(3,5); END;

*
ERROR at line 1:
ORA-20000: the empid 5 already exists
ORA-06512: at "MAG.SP_TEST345", line 9
ORA-06512: at line 1

--
-- empid 26 does not exist.
--
mag@itloaner1_local >  exec sp_test345(3,26);

PL/SQL procedure successfully completed.