Oracle : Handling unique constraint, gracefully. ORA-00001

ORA-00001: unique constraint (<table>) violated

Resolutions:

* Find offending data and clean it.
Other way below(experimental form):SQL> select count(1) from emp;

  COUNT(1)
----------
        14

SQL> create table uemp as select * from emp;

Table created.

SQL> --------above is test table with 14 rows created;
SQL> create unique index i_uemp on uemp(empno);

Index created.

SQL> -----unique index created
SQL> ----trying to insert same 14 rows again
SQL> insert into uemp select * from emp;
insert into uemp select * from emp
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.I_UEMP) violated   <=Problem


SQL> ----Failed because of unique constraint, an expected behavior
SQL> insert /*+ ignore_row_on_dupkey_index */ into uemp select * from emp;
insert /*+ ignore_row_on_dupkey_index */ into uemp select * from emp
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.I_UEMP) violated

Note : Below is just syntax check, put here so that same is avoided
SQL> insert /*+ ignore_row_on_dupkey_index(SCOTT.I_UEMP) */ into uemp select * from emp;
insert /*+ ignore_row_on_dupkey_index(SCOTT.I_UEMP) */ into uemp select * from emp
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.I_UEMP) violated

----Success
SQL> insert /*+ ignore_row_on_dupkey_index(uemp, I_UEMP)  */ into uemp select * from emp;

0 rows created.

SQL> select count(1) from uemp;

  COUNT(1)
----------
        14

SQL> -----Basically insert succeeded i.e. didnot throw error at the same time didnot insert data either.
SQL> select empno from uemp;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

     EMPNO
----------
      7900
      7902
      7934

14 rows selected.

SQL> delete from uemp where empno>7900;

2 rows deleted.

SQL> commit;

Commit complete.

--Attention here: See only non offending(non duplicate) rows inserted. And no error for rest of the data.
SQL> insert /*+ ignore_row_on_dupkey_index(uemp, I_UEMP)  */ into uemp select * from emp;

2 rows created.

SQL> -----only delta rows or unique rows inserted. Very Nice.
SQL> delete from uemp where empno>7900;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> insert /*+ ignore_row_on_dupkey_index(uemp, I_UEMP)  */ into uemp select * from emp;

2 rows created.

SQL> -----only delta rows or unique rows inserted. Very Nice.
SQL>

2 rows deleted.