oracle-databaseunixsqlpluskshon-duplicate-key

How can i ignore dupkey with sqlplus?


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


Solution

  • 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.