How can i ignore dupkey with sqlplus?

Question

I want to write a sql plus error for when the oracle find a record, or more records, that already exist and just ignore it/them. This is a example:

sqlError=`egrep "ORA-[0-9][0-9][0-9][0-9][0-9]" ${FILE_SPOOL_DAT} | awk '{print $0}';`   
if test ! -f ${FILE_SPOOL_DAT}
then
   echo "Error request " >> ${FILE_SPOOL_DAT}
else
   if [ ! "$sqlError" = "" ] #controls if the variable $sqlError contains a value different from spaces, i think this is the point to change
   then
      echo "Error $sqlError" >> ${FILE_SPOOL_DAT} 
   fi
fi

In this example sqlplus controls if the variable $sqlError contains a value different from spaces. How can i change this condition put the DUPKEY error? Thanks

Answer 1

If you're using 11g, the IGNORE_ROW_ON_DUPKEY_INDEX hint can help.

SQL> create table table1(a number primary key);

Table created.

SQL> insert into table1 values(1);

1 row created.

SQL> insert into table1 values(1);
insert into table1 values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (JHELLER.SYS_C00810741) violated


SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(table1(a))*/ into table1 values(1);

0 rows created.