Is there any way we can skip the account which is raising exception i.e. duplicate entry of that acc-CodePudding

Suppose we have a table which consist number of records for the user accounts. Now we are running a procedure which is picking accounts from 1 system to source i.e. oracle. We have added constraints to the table in oracle for ID which should be unique. In that procedure we have added an exception which will throw the error when unique constraint is violated everytime and procedure will fail. Now my question is- Is there any way procedure can skip that account which is already present in the source table which is causing exception and rest insert go fine and procedure will be completed successfully?

I try to raise the exception but procedure is getting failed after raising the exception for unique key constraint error.

CodePudding user response：

You didn't explain how exactly you're doing it, so I'll presume it is some kind of a cursor FOR loop which fetches user accounts from source table and inserts them into a target table.

Use inner (within the loop) begin-exception-end block which will handle exception (basically, you'll just ignore it) and proceed with another user account.

Something like this:

begin
  for cur_r in (select user_account, ... from source_table) loop
  
    -- start inner BEGIN-EXCEPTION-END block
    begin
      insert into target_table (...) values (cur_r.user_account, ...);
    
    -- handle exception
    exception
      when dup_val_on_index then
        -- ignore it
        null;
    end;
    -- end innser BEGIN-EXCEPTION-END block
  end loop;
end;

On the other hand, can't you avoid duplicates in query?

for cur_r in (select user_account, ...
              from source_table s
              where not exists (select null
                                from target_table t
                                where t.user_account = s.user_account
                               )
             ) loop

[EDIT] You commented that you're either updating or inserting values - that's actually what merge (also known as upsert) does:

merge into target_table a
  using source_table b
  on (a.user_account = b.user_account)
when matched then update set
  a.name = b.name,
  a.address = b.address
when not matched then insert (user_account, name, address)
  values (b.user_accout, b.name, b.address);

CodePudding user response：

You can also use DBMS_ERRLOG package to create a table where the errors will be logged, there is an example in LiveSQL: https://livesql.oracle.com/apex/livesql/file/content_JNEICX6W0LNOA88CQIXO9A22A.html and discussion in AskTom https://asktom.oracle.com/pls/apex/asktom.search?tag=error-logging-using-log-errors-into