CODEWITHSUNDEEP
sqloracle-database
ETS
ETS

Reputation: 546

How to skip unique constraint error

I am trying to insert values into sql table, but I am getting this error in sql query

SQL Error: ORA-00001: unique constraint (uniqueKey) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
       For Trusted Oracle configured in DBMS MAC mode, you may see
       this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

Is there a way to skip this error and continue insert. Something like this

try
   insert query
catch (unique constraint error)
   continue inserting other values

Upvotes: 3

Views: 19820

Answers (3)

are
are

Reputation: 2615

simple sample is insert in for loop and ignore exceptions:

begin
  for rc in (select * from <your query> loop
  begin
    insert into t1(...) values (...);
  exceptions when others then 
    null;--ignore any exceptions do nothing 
  end;
  end loop;
end

other sample - same idea but use FORALL bulk operation and SAVE EXCEPTIONS 

declare
    cursor C is
    select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
           decode( mod(rownum,100000), 1, rpad('*',20,'*'), OBJECT_TYPE ) object_type,
           CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY
      from big_table;
    type array is table of c%rowtype;
    l_data array;

    dml_errors EXCEPTION;
    PRAGMA exception_init(dml_errors, -24381);
    l_errors number;
    l_errno    number;
    l_msg    varchar2(4000);
    l_idx    number;
begin
    open c;
    loop
        fetch c bulk collect into l_data limit 100;
        begin
            forall i in 1 .. l_data.count SAVE EXCEPTIONS
                insert into t2 values l_data(i);
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop
                    l_errno := sql%bulk_exceptions(i).error_code;
                    --do smth with the exceptions
                end loop;
        end;
        exit when c%notfound;
    end loop;
    close c;
end;

more information see on AskTom and OraMagazine

https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:1422998100346727312

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html

Upvotes: 0

Husqvik
Husqvik

Reputation: 5809

There exists hint ignore_row_on_dupkey_index(<table name>, <unique index name>).

HUSQVIK@hq_pdb_tcp> CREATE TABLE tmp (val NUMBER CONSTRAINT pk_tmp PRIMARY KEY);

Table created.

HUSQVIK@hq_pdb_tcp> INSERT /*+ ignore_row_on_dupkey_index(tmp, pk_tmp) */ INTO tmp (val) SELECT 1 FROM DUAL CONNECT BY LEVEL <= 3;

1 row created.

See that I insert three values of 1 and only one row was created.

Upvotes: 10

Andrew
Andrew

Reputation: 27294

There is a LOG ERRORS clause which allows you to log the rows that cause errors in to an error table - this error table is created using a DBMS package:

DBMS_ERRLOG.CREATE_ERROR_LOG(table_being_inserted_into ,name_of_table_for_errors ,NULL,NULL,TRUE);

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_errlog.htm

Function Signature:

 DBMS_ERRLOG.CREATE_ERROR_LOG (
   dml_table_name            IN VARCHAR2,
   err_log_table_name        IN VARCHAR2 := NULL,
   err_log_table_owner       IN VARCHAR2 := NULL,
   err_log_table_space       IN VARCHAR2 := NULL,
   skip_unsupported          IN BOOLEAN := FALSE);

Then in your insert statement you end it with a log errors clause:

LOG ERRORS INTO your_error_table_name ( 'description of your choosing' ) REJECT LIMIT UNLIMITED;

You can choose to accept a reject limit of a fixed number, allowing you to specify in effect a tolerance to errors before it throws a real error instead of just allowing the row to be place in an error table.

Upvotes: 0

Related Questions