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