SQL & PL/SQL

Hi

I am trying to handle the errors between each DML statements. when the code runs and it fails between the DML statement and half of the tables are committed and gives me the unexpected result.

Please let me know how to avoid this?

Is every DML statement to be written with each error handling block? or any other better way than this using Savepoint?

begin
insert statement 1;
commit;


insert statement 2;
commit;


update statement statement 1
commit;


delete statement 1;
commit;

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
  proc_stat_util.write_msg('E', 'Unique constraint Error: Please check if any record is there at '|| v_tablename || 'table');
  raise_application_error(-20001, 'Unique constraint Error: Please check if any record is there at '|| v_tablename || 'table');
  WHEN OTHERS THEN
  ROLLBACK;
  RAISE_APPLICATION_ERROR(-20001,'ORACLE ERROR: '||DBMS_UTILITY.FORMAT_ERROR_STACK );
End;

Thanks a lot in advance